Re: Macro function which can read Excel spreadsheets with multiple tab

Scott_Mitchell — Thu, 18 Jul 2013 08:36:56 GMT

I don't know what SAS modules you have but this is an old version that I had laying around from a past role. There may be more efficient functions around that make some of this code redundant, but at least it gives you a starting point.

dm wpgm 'clear log' wpgm;

ods listing close;

*************************************************************************;

* Macroimporter9.sas *;

* Version 1.72 *;

* Date 2004 *;

* Purpose: to import all excel sheets in an xls file to SAS datasets *;

* MAKE SURE YOU MAKE ALL CHANGES WHERE YOU SEE THE ARROWS >>>>>>>> *;

* SCAN THE PROGRAM FOR THESE *;

* *;

* Limitations: *;

* Spreadsheet name may not contain the following: *;

* 1. A formfeed character in the name, but then who would *;

* 2. A ` or ' character may be a problem depending on how it is used *;

* 3. If there is a $ in the sheetname we will compress it out *;

* Modifications *;

* 2-25-2004 added sas name compression support *;

* added european currency support for british pound and euro *;

* august 2004 Added code to handle ranges in excel sheets *;

* august 2004 Added code to clear xls lock *;

* november 2004 Added code to access sheetname instead of the reverse *;

* compressed out the last dollarsign *;

* compressed out the last spaces in the sas name *;

* July 2006 added two routines to append to a master and delete *;

* the master at the beginning of the run *;

*************************************************************************;

* These options are used for debugging *;

* You can turn these off if you wish *;

options mprint nomfile macrogen symbolgen mlogic;

*options mprint;

********************************************;

* Now to start the macro *;

* the first loop here takes all the *;

* dataset names and figures out how many *;

* there are and creates a counter *;

* see the actual macro call for the *;

* explanation of the parameters *;

********************************************;

%macro imploop(xlsfile1,dbms,libr,cmprs,getnames,mixed,scantext,usedate,scantime,appender,

mastlib,masterds,delmaster);

********************************************************************;

* This routine cleans out the master dataset if the user specifies *;

* delete as a parameter to clean it *;

********************************************************************;

%if &delmaster=delete %then %do;

proc datasets library=&mastlib;

delete &masterds;

run;

%end;

******************************************************************;

* This part loads the excel workbook names into a SAS database *;

* We then setup a detecter to find any non alphanumerics in the *;

* the sheet name because the import procedure treats these *;

* in a different manner *;

******************************************************************;

libname xcl &xlsfile1;

ods trace on;

ods output "Library Members"=dirvol1;

proc contents data=xcl._all_;

run; quit;

ods output close;

data dirvol2; set dirvol1;

backward=left(trim(reverse(name)));

valuechk=substr(backward,1,1);

run;

data dirvol3; set dirvol2;

if valuechk="'" or (valuechk ="$");

keep name valuechk;

rename name=xlssht1;

run; quit;

data setup; set dirvol3;

***************************************************;

* This translate table here translates any *;

* unusual characters into names that the *;

* SAS dataset names allow. SAS dataset *;

* names allow for _1234567890 and letters only. *;

* So this table translates any of those *;

* characters into new values that are acceptable *;

* I tried to think of reasonable translations here*;

* but feel free to change this as you see fit *;

* With the translate function the to character *;

* comes first and then the from character *;

* With the tranwrd function the from character *;

* comes first *;

* *;

***************************************************;

xlssht2=TRANSLATE(TRIM(XLSSHT1),'_',' ');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_',"'");

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_',"~");

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','`');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'','$');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','#');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','!');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','@');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','%');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','^');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','*');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','(');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_',')');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','+');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','=');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','-');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','{');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','[');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_',']');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','}');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','\');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','|');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','<');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','>');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_',',');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','.');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','?');

* british pound sign and euro *;

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','£');

xlssht2=TRANSLATE(TRIM(XLSSHT2),'_','€');

xlssht2=TRANWRD(XLSSHT2,"&","and");

xlssht2=COMPRESS(XLSSHT2,' ');

***********************************************;

* Now take the translated name and turn it *;

* Into a SAS name *;

* If compression is turned on we remove the *;

* underscores from the name *;

***********************************************;

%if &cmprs=yes %then %do;

xlssht2=compress(xlssht2,'_');

sasname=left(trim("&libr"))||'.'||left(trim(xlssht2));

%end;

%else %do;

sasname=left(trim("&libr"))||'.'||left(trim(xlssht2));

%end;

**************************************************************************;

* Because the import procedure works differently with access tables *;

* we have to test for this here *;

**************************************************************************;

data setupm1;

set setup;

dbms1=symget('dbms');

dbms1=left(trim(dbms1));

if ((dbms1='access97') or (dbms1='access') or (dbms1='access2000')) then do;

quoter='"';

xlssht1b=xlssht1;

end;

if ((dbms1='excel97') or dbms1=('excel') or (dbms1='excel2000')) then do;

quoter='"';

xlssht1b=xlssht1;

end;

run;

data _null_; set setupm1 end=last;

call symput('datafilm'||left(_n_),trim(xlssht1b));

call symput('outfilem'||left(_n_),trim(sasname));

if last then call symput('counter',_n_);

run;

*************************************************;

* this macro loop goes thru the counter and *;

* imports the individual sheets to SAS datasets *;

* changes the dbms based on the value *;

*************************************************;

%do i= 1 %to &counter;

%put &&outfilem&i;

%put &dbms;

%put datafilem&i;

***********************************************************;

* Check the DBMS before going into the import code *;

***********************************************************;

* This portion does the import of the ACCESS code and XLS *;

***********************************************************;

%if ((&dbms=access97) or (&dbms=access) or (&dbms=access2000)) %then %do;

proc import out=&&outfilem&i

datatable="%superq(datafilm&i)"

dbms=&dbms replace;

database=&xlsfile1;

scanmemo=&getnames;

usedate=&usedate;

scantime=&scantime;

run;

%end;

%else %do;

proc import datafile=&xlsfile1

out=&&outfilem&i

dbms=&dbms replace;

* sheet="%superq(datafilm&i)";

range="%superq(datafilm&i)";

getnames=&getnames;

mixed=&mixed;

scantext=&scantext;

usedate=&usedate;

scantime=&scantime;

%end;

******************************************************************;

* this appends on to the master if the user requests it *;

******************************************************************;

%if &appender=append %then %do;

%do i= 1 %to &counter;

proc append base=&mastlib..&masterds data=&&outfilem&i FORCE;

run;

%end;

%put _all_;

%mend imploop;

*************************************************************;

* Now we call the macro here *;

* 1. name of the excel sheet here *;

*************************************************************;

* 2. and the type of database it is *;

*************************************************************;

* 3. the libref you wish to store the sas datasets in *;

* the default is work *;

*************************************************************;

* 4. dsname compression -default is NO *;

* whether to remove underscores *;

* from the default sas dataset names *;

* for example the sheet 'my#numbers' becomes my_numbers *;

* dataset in SAS. When compression is turned on the *;

* sas dataset name becomes mynumbers *;

*************************************************************;

* 5. Getnames - yes or no - default is YES *;

* whether the first row of the *;

* sheet is used to extract the names of the variables *;

* This parameter is also used to set SCANMEMO when *

* importing ACCESS databases *;

*************************************************************;

* 6. Mixed - yes or no - Default is NO *;

* specifies whether to convert numeric data values into *;

* character data values data for a column with mixed data *;

* types. This option is valid only while importing data *;

* from Excel. The default is NO, which means that numeric *;

* data will be imported as missing values in a character *;

* column. If MIXED_DATATYPE=YES, the engine will assign a *;

* SAS character type for the column and convert all numeric *;

* data values to characters data. This option is only valid *;

* while reading(importing) data into SAS. *;

*************************************************************;

* 7. Scantext Yes or No, default is YES *;

* specifies whether to scan the length of text data for *;

* a DBMS column and use the length of the longest string *;

* data found as SAS column width. For MS Excel, This *;

* applies to all character data type columns. For *;

* MS Access, this applies to MEMO data type fields only *;

* and doesnot apply to TEXT(less than 256 characters long)*;

* fields. If the maximum length found is greater than *;

* what is specified in DBMAX_TEXT=, the small value *;

* specified in DBMAX_TEXT= will be applied. *;

*************************************************************;

* 8. Usedate Yes or No - Default is YES *;

* specifies whether to use DATE. format for date/time *;

* columns/fileds while importing data from MS Excel or *;

* MS Access file. YES means to use the SAS DATE. format,*;

* NO means to use SAS DATETIME. format. *;

*************************************************************;

* 9. Scantime Yes or No - Default is YES *;

* specifies whether to scan all row values in a date/time*;

* data type column and automatically determine to use the*;

* TIME. format if only time values(i.e. no date or *;

* datetime values) found in the column. To import data,*;

* by default, this libname engine assigns SAS DATE. *;

* format for date/time columns in MS Excel and assigns *;

* SAS DATETIME. format for MS Access date/time fields. *;

* However, if this option is turned on, a column with only *;

* time values will be assigned with a SAS TIME. format. *;

*************************************************************;

* 10. Appender - whether you want to create a master dataset *;

* valid values are append or no *;

*****************************************************************;

* 11. Libref of the master dataset, note that you may need *;

* to specify a separate libname statement before you run *;

* if it is not work, sasuser, or other preasssigned library *;

*****************************************************************;

* 12. the actual name of the master dataset *;

*****************************************************************;

* 13. Whether you want to clean out the master dataset at the *;

* beginning of the run, valid values are delete and no *;

*****************************************************************;

* >>>>>>>>>>>>>>>>>>>>>>>> Change the parameters here <<<<<<<<<<<<<<<<<<<*;

* Sheetname,dbms,libref,compress,getnames,mixed,scantext,usedate,scantime *;

* For access getnames becomes scanmemo *;

*****************************************************************************;

%imploop("k:\ruzsa\my importer\multisheet3.xls", /* name of sheet */

excel2000, /* dbms */

work, /* libref for sas datasets */

no, /* remove underscores when substitute for special characters */

yes, /* getnames */

no, /* mixed data */

yes, /* scan text */

yes, /* use date */

yes, /* scantime */

no, /* append on to a master dataset, append or no */

work, /* libref of the master dataset */

master, /* name of the master dataset */

no); /* delete master dataset at start, delete or no */

*%imploop("k:\sasszo\office 2000 tests\2002db1.mdb",access2000,work,no,yes,no,yes,yes,yes);

run;

quit;

libname xcl clear;

ods listing;

topic Macro function which can read Excel spreadsheets with multiple tab in SAS Programming

Macro function which can read Excel spreadsheets with multiple tab

Re: Macro function which can read Excel spreadsheets with multiple tab

Re: Macro function which can read Excel spreadsheets with multiple tab