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; %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; %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;
... View more
