SAS en Excel. Martine Beullens Jan Ooghe

SAS en Excel

Martine Beullens Jan Ooghe

K. U . LEU V EN

-

LU D I T

Inhoud

Excel als database Inlezen van Excel gegevens in SAS Uitvoer van SAS gegevens naar Excel SAS gebruiken binnen Excel

K. U . LEU V EN

-

LU D I T

Excel als database

Te verwachten problemen

relationele tabel (SAS) versus Spreadsheet (Excel) • Relationele tabel verwacht bepaalde structuur • data type vast voor hele kolom < -> datatype per cel • Gegevens kunnen op een specifieke plaats staan, gedefineerd door een « named range » Excel specifiek • Enkel datetime datatype • Ander datum bereik • Dollar-teken in naam van het werkblad • Geen beperkingen op namen van variabelen

K. U . LEU V EN

-

LU D I T

Inlezen van excel bestanden

K. U . LEU V EN

-

LU D I T

Excel bestanden inlezen

Tab delimited of CSV bestand Xls bestand inlezen Libname Excel Andere methoden

ODBC DDE

K. U . LEU V EN

-

LU D I T

Tab delimited of CSV bestand

Exporteer XLS naar CSV of tab delimited Gebruik de import wizard om de data in te lezen in een SAS dataset

K. U . LEU V EN

-

LU D I T

SAS Import Wizard - CSV •Kies het bestandstype van de input data

K. U . LEU V EN

-

LU D I T

SAS Import Wizard - CSV

Locatie van het CSV bestand dat moet geimporteerd worden

K. U . LEU V EN

-

LU D I T


Mogelijke opties voor een bestand met delimiter

Welke delimiter Zitten variabelenamen in het bestand Hoeveel rijen scannen om het type te bepalen

K. U . LEU V EN

-

LU D I T


Library en naam die gebruikt wordt voor de nieuwe dataset

K. U . LEU V EN

-

LU D I T


Het is mogelijk om de gegenereerde code op te slaan

K. U . LEU V EN

-

LU D I T


Gegenereerde code

PROC IMPORT OUT= WORK.ae DATAFILE= "C:\MijnDirectory\ae.csv" DBMS=CSV REPLACE; GETNAMES=YES; DATAROW=2; RUN;

K. U . LEU V EN

-

LU D I T

Proc Import syntax

PROC I MPORT DATAFI LE= "filename“ OUT= < libref.> SAS-data-set < (SAS-data-set-options)> < DBMS= identifier> < REPLACE> ; < data-source-statement(s);> run;

Datafile

Out

DBMS

Complete directory en naam van het bestand of een fileref SAS dataset Bepaalt het type bestand dat moet geimporteerd worden • csv, excel

Replace

Duidt aan dat de OUT dataset mag overschreven worden

K. U . LEU V EN

-

LU D I T

Proc Import syntax

Datasource statements voor DBMS= CSV

GETNAMES= YES | NO • Gebruik de data van de eerste rij als kolomnamen

DATAROW= 1 to 327672 (2) • Begin data in te lezen vanaf deze rij

GUESSINGROWS= 1 to 327672 () • Bepaalt tot welke rij er moet gelezen worden om een datatype te bepalen

K. U . LEU V EN

-

LU D I T

Opgelet met datum en tijd variabelen

Import wizard, proc import lezen data/ tijd niet altijd goed in (SN-013484). Pas de gegenereerde datastep aan Recall geeft de gegenereerde datastep weer

K. U . LEU V EN

-

LU D I T


Recall data WORK.ae; %let _EFIERR_ = 0; /*set the ERROR detection macro variable*/ infile ’C:\MijnDirectory\ae.csv’ delimiter = ’,’ MISSOVER DSD lrecl=32767 firstobs=2 ; informat patient best32. ; informat datum ddmmyy10. ; informat uur $5. ; /* TIJD is ingelezen als karakter */ informat graad best32. ; informat beschrijving_van_aandoening $41. ; format patient best12. ; format datum ddmmyy10. ; format uur $5. ; format graad best12. ; format beschrijving_van_aandoening $41. ; input patient datum uur $ graad beschrijving_van_aandoening $; /*set ERROR detection macro variable*/ if _ERROR_ then call symputx(’_EFIERR_’,1); run;

K. U . LEU V EN

-

LU D I T

Xls bestand inlezen

Import wizard Proc import Libname excel

K. U . LEU V EN

-

LU D I T

SAS Import Wizard - XLS

Verschillende versies van excel kunnen gekozen worden

K. U . LEU V EN

-

LU D I T


Blader naar de werkboek die geimporteerd moet worden

Selecteer het werkblad dat geimporteerd moet worden

K. U . LEU V EN

-

LU D I T


Verschillende opties zijn mogelijk

Variabele namen Hoe omgaan met verschillende types in dezelfde kolom Max. lengte karakter variabelen Hoe omgaan met data en tijd

K. U . LEU V EN

-

LU D I T


Library en dataset waar de geimporteerde data moeten opgeslaan worden

K. U . LEU V EN

-

LU D I T


Gegenereerde code kan bewaard worden

K. U . LEU V EN

-

LU D I T


Gegenereerde code

PROC IMPORT OUT= WORK.xls DATAFILE= "C:\MijnDirectory\ae.xls" DBMS=EXCEL REPLACE; SHEET="Nevenwerkingen$"; GETNAMES=YES; MIXED=YES; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN;

K. U . LEU V EN

-

LU D I T

Proc Import syntax

Datasource statements voor dbms= excel

RANGE= Range Name | Absolute Range Value( ’Sheet1$A1:C4‘) SHEET= Sheet Name MIXED= YES | NO

SCANTEXT= YES | NO

SCANTIME= YES | NO

USEDATE= YES | NO

TEXTSIZE= 1 to 32767(1024)

DBSASLABEL= COMPAT | NONE

• • • •

Converteer numerieke waarden naar een karakterveld indien de kolom verschillende soorten datatypes bevat Scan de lengtes van de waarden in de kolomen en gebruik dat om de lengte van de variabelen in SAS te bepalen

Scan alle rijen van een DATETIME dataveld. Gebruik TI ME data type indien er enkel tijdswaarden worden gevonden Gebruik het DATE formaat voor DATETI ME velden. Indien NO wordt het DATETI ME formaat gebruikt

•

Bepaal de maximale lengte van een karakterveld dat mag worden ingelezen

•

De kolomnamen worden ook als label gebruikt

K. U . LEU V EN

-

LU D I T

Proc Import – type bepaling

Bepalen van datatype

SAS gebruikt de MS-JET engine Standaard worden de eerste 8 rijen onderzocht. Het meest voorkomende type bepaalt het type voor de import.

K. U . LEU V EN

-

LU D I T

Excel libname engine

Een libname definieert een SAS library

Verzameling van bestanden die SAS herkend en groepeert onder 1 logische eenheid Standaard zijn dit SAS datasets, catalogs, …

SAS heeft verschillende engines om andere bestanden dan SAS bestanden te herkennen Vanaf SAS 9 is er ook een engine voor Excel bestanden :

LibName MyName excel "\ path\ fileName.xls" ;

K. U . LEU V EN

-

LU D I T


3 werkbladen in het excel bestand.

K. U . LEU V EN

-

LU D I T


libname xls excel ’C:\ MijnDirectory\ cultivar.xls’;

K. U . LEU V EN

-

LU D I T


Excel namen hebben geen beperking, De naam van een werkblad bevat bv. een $-teken, kan spaties bevatten, …

Options validVarName= any

Gebruik een “name literal” • “werkbladnaam$”n

Een naam met spaties wordt in Excel met enkele aanhalingstekens omringd • “’naam van het werkblad$’”n

Wel nog altijd beperkt tot een lengte van 32 karakters Datasource opties van proc import zijn ook geldig

Usedate/scantime/ mixed/… K. U . LEU V EN

-

LU D I T

K. U . LEU V EN

-

LU D I T

Wat kan de libname engine doen

Nieuwe werkboek maken Nieuw werkblad maken, eventueel met een specifieke range Data schrijven naar een bestaande named range. Data lezen van een bestaand werkblad of named range. Data wissen van een werkblad of named range. Excel moet niet geinstalleerd zijn op de PC.

K. U . LEU V EN

-

LU D I T

Wat kan libname excel niet doen

Werkblad hernoemen Werkbladen verwijderen uit een werkboek Werkboek verwijderen Formatteringen toepassen of veranderen Cellen die formules bevaten verwijderen Een formule schrijven in een cell.

K. U . LEU V EN

-

LU D I T

Andere methoden

DDE (Dynamic Data Exchange) ODBC (Open DataBase Conectivity)

ODBC datasource reeds op het systeem gedefinieerd of enkel binnen het SAS programma

K. U . LEU V EN

-

LU D I T

DDE

Hoe werkt het ?

Eerst excel opstarten en xls bestand openen, kan vanuit SAS Excel 4 macro commandos worden gebruikt Definieer filename voor het uitvoeren van commando’s en voor het inlezen van data

K. U . LEU V EN

-

LU D I T

DDE - open een xls bestand /* options to return control to SAS program after */ /* an X command is started */ options noxwait noxsync; Start Excel /* open and start excel */ x ’"C:\Program Files\Microsoft Office\OFFICE11\excel.exe"’; /* wait for a few seconds to allow excel to come up */ data _null_; Wacht even x=sleep(5); run; /* pass to excel the following MACRO commands to tell it what to do and on what cols and rows to do it to */ filename cmds dde ’Excel|system’; data _null_; file cmds; Open een XLS bestand put ’[app.minimize()]’; put ’[open("C:\mijndirectory\dde.xls")]’; run;

K. U . LEU V EN

-

LU D I T

DDE - Lees data in /* do a datastep against the sorted excel spreadsheet by reading it in and printing the vars */ filename in ddeLeg ’Excel|Sheet1!R1C1:R5C2’; een fileref naar het open excel bestand

data test; infile in; input a b $; run;

filename cmds ; filename in;

Lees gegevens in

Verwijder de referenties

K. U . LEU V EN

-

LU D I T

Excel inlezen via ODBC

Een bestaande ODBC datasource kan gebruikt worden SAS kan in een dialoog vragen naar de nodige informatie om de ODBC connectie op te zetten Je kan de volledige definitie meegeven in de libname statement

K. U . LEU V EN

-

LU D I T

ODBC datasource aanmaken

Via Start-> control panel-> Administrative tools-> Data Sources (ODBC)

K. U . LEU V EN

-

LU D I T

ODBC libname libname xlsodbc odbc dsn=MijnNaam;

K. U . LEU V EN

-

LU D I T

ODBC libname

Je kan de informatie om een ODBC datasource aan te maken ook interactief doorgeven aan SAS

libname test odbc prompt;

Je kan ook de volledige informatie in de libname coderen

libname test odbc noprompt= ’DSN= Excel Files; DBQ= C:\ MijnDirectory\ ae.xls; DefaultDir= C:\ MijnDirectory; DriverId= 790; Driver= { Microsoft Excel Driver(* .xls)} ; MaxBufferSize= 2048; PageTimeout= 5;’; Deze informatie kan je ook zien met %put &sysdbmsg nadat een geslaagde “libname xxx odbc prompt;” is uitgevoerd K. U . LEU V EN

-

LU D I T

Voorbeeld - Alle excel bestanden in een directory importeren

Welke excel bestanden bevinden zich in de directory Lees elk excel bestand in Exporteer elk werkblad naar een dataset Gebruik macro code om alle excel bestanden te importeren

K. U . LEU V EN

-

LU D I T

Voorbeeld – bepalen van alle excel bestanden filename dir pipe "dir /B C:\MijnDirectory\*.xls"; data _null_; Voer commando uit infile dir; input; versluis resultaat put _infile_; naar het bestand run;

Automatische variabele bevat de ingelezen lijn

DH[OV FKDUDFWHUPDMRULW\[OV &XOWLYDU[OV GGH[OV PLVVLQJ[OV QRPDMRULW\[OV 1XPHULFPDMRULW\[OV YHUWUHNNHQGHSDVVDJLHUV[OV YHUWUHNNHQGH SDVVDJLHUV[OV 127(UHFRUGVZHUHUHDGIURPWKHLQILOH ',5 7KHPLQLPXPUHFRUGOHQJWKZDV 7KHPD[LPXPUHFRUGOHQJWKZDV

K. U . LEU V EN

-

LU D I T

Voorbeeld – maak macro variabelen van de bestandsnamen %let path=C:\Personal\SAS\documentation\Excel\data\in; filename dir pipe "dir /B &path.\*.xls"; %let nFiles=0; Automatische variabele _n_ bevat de data _null_; observatienummer infile dir end=e; input; call symput(cats(’file’,_n_),cats("&path.\",_infile_)); if e then call symput(’nFiles’,left(put(_n_,8.0))); run;

Bv. Macrovariabele file1krijgt de bestandsnaam van het eerste excel bestand in de directory listing Bij de laatste observatie wordt een macro variabele aangemaakt met het aantal bestanden

K. U . LEU V EN

-

LU D I T

Voorbeeld – bepalen van alle werkbladen in een werkboek SAS view sashelp.vmember bevat informatie over alle gebruikte libnames. 1 observatie per member in de library. libname xls "C:\Personal\SAS\documentation\Excel\data\in\ae.xls“; data _null_; set sashelp.vmember(where=(libname="XLS")) end=e; call symput(cats(’XLStable’,_n_),memname); call symput(cats(’SAStable’,_n_),tranwrd(memname,"$","S")); if e then call symput(’nTables’,left(put(_n_,8.0))); run;

Aanmaken van een macrovariabele per dataset Tranwrd vertaalt het teken $ in S zodat de naam kan gebruikt worden als dataset naam

K. U . LEU V EN

-

LU D I T

Voorbeeld – Excel sheet kopieren in een SAS dataset data &SAStable1; set xls."&XLStable1"n; run;

K. U . LEU V EN

-

LU D I T

%macro exportExcel; options validvarname=any; filename dir pipe "dir /B &path.\*.xls"; %let nFile=0; data _null_; infile dir end=e; input; call symput(cats(’file’,_n_),cats("&path.\",_infile_)); if e then call symput(’nFiles’,left(put(_n_,8.0))); run; %do i=1 %to &nFiles; libname xls "&&file&i"; %put reading &&file&i; %let nTables=0; data _null_; set sashelp.vmember(where=(libname="XLS")) end=e; call symput(cats(’XLStable’,_n_),memname); call symput(cats(’SAStable’,_n_),tranwrd(memname,"$","S")); if e then call symput(’nTables’,left(put(_n_,8.0))); run; %do j=1 %to &nTables; data "&&sastable&j"n; set xls."&&xlstable&j"n; run; %end; libname xls; %end; %mend; %let path=C:\MijnDirectory; %exportExcel;

Voorbeeld – Macro samenstellen

K. U . LEU V EN

-

LU D I T

Van SAS naar Excel?

Referenties ¾

SAS Institute Inc. 2004, SAS 9.1 Output Delivery System: User’s Guide. Cary, NC: SAS Institute Inc.

¾

PC files reference : http:/ / support.sas.com/ documentation/ onlinedoc/ 91pdf/ sasdoc_91/ access_pc files_7056.pdf

¾

Proc import syntax in onlineDoc http:/ / support.sas.com/ onlinedoc/ 913/ getDoc/ en/ proc.hlp/ a000308089.htm

¾

Proc export syntax in onlineDoc http:/ / support.sas.com/ onlinedoc/ 913/ getDoc/ en/ proc.hlp/ a000393174.htm

¾

SAS Base Community :The ExcelXP Tagset and Microsoft Excel http:/ / support.sas.com/ rnd/ base/ topics/ odsmarkup/ excelxp_demo.html

¾

ODS FAQs and Concepts http:/ / support.sas.com/ rnd/ base/ topics/ templateFAQ/ Template.html

K. U . LEU V EN

-

LU D I T

Van SAS naar Excel? Referenties ¾

papers in de SUGI proceedings :

¾

So your data is in Excel, Ed Heaton, Westat Paper 20-31

¾

SAS® to Publishable Excel... Seamlessly – Using ODS, XML, and Other Tricks, Marje Fecht, Harry Droogendyk: SUGI31, Paper 106-31

¾

Creating AND Importing Multi-Sheet Excel Workbooks the Easy Way with SAS® , Vince DelGobbo: SUGI31, Paper 115-31

¾

ODS Markup: The SAS Reports You' ve Always Dreamed Of , Eric A. Gebhart: SUGI30, Paper 085-30

http:/ / support.sas.com/ events/ sasglobalforum/ previous/ index.html

K. U . LEU V EN

-

LU D I T

De Creative Commens Naamsvermeliding-Nietcommercieel-GelijkDelen 2.0 Belgium Licentie is van toepassing op dit werk. Ga naar http://creativecommons.org/licenses/by-nc-sa/2.0/be/deed.nl of stuur een brief naar Creative Commons, 559 Nathan Abbott Way, Stanford, Californië 94305, VS om deze licentie te bekijken.

K. U . LEU V EN

-

LU D I T

SAS en Excel. Martine Beullens Jan Ooghe

Recommend Documents