dm wpgm 'clear log' wpgm; ods listing close; *************************************************************************; * Macroimporter9accesspw *; * Version 1.9 *; * Date 2009 *; * Purpose: to import all access tables in an mdb file to SAS datasets *; * Copyright SAS Institute All rights reserved *; * There are no liabilities implied by using this code *; * *; * Limitations: *; * Table 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 *; * Modifications *; * July 2006 added two routines to append to a master and delete *; * the master at the beginning of the run *; * March 2009 added a password routine *; *************************************************************************; * 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,scanmemo,memosize,usedate,scantime,appender, mastlib,masterds,delmaster,pass1); %put _all_; ********************************************************************; * 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; quit; %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 DBPWD="&pass1";; 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; 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; 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 *; ***********************************************************; proc import out=&&outfilem&i datatable="%superq(datafilm&i)" dbms=&dbms replace; database=&xlsfile1; scanmemo=&scanmemo; memosize=&memosize; usedate=&usedate; scantime=&scantime; DBPWD="&pass1"; run; %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. ScanMemo Yes or No, default is YES *; * *; * scans the length of data for memo fields and *; * uses the length of the longest string data *; * that it finds as the SAS column width. However, *; * if the maximum length *; * that it finds is greater than what is specified in the *; * MEMOSIZE= option, then the smaller value that is specified *; * in MEMOSIZE= will be applied as the SAS variable width *; ************************************************************; * 6. Memosize *; * MEMOSIZE="field-length" *; * specifies the field length for importing *; * Microsoft Access Memo fields. *; ************************************************************; *************************************************************; * 7. 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. *; *************************************************************; * 8. 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. *; *************************************************************; * 9. Appender - whether you want to create a master dataset *; * valid values are append or no *; *****************************************************************; * 10. 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 *; *****************************************************************; * 11. the actual name of the master dataset *; *****************************************************************; * 12. Whether you want to clean out the master dataset at the *; * beginning of the run, valid values are delete and no *; *****************************************************************; * 13. The password that you want to use with the database *; *****************************************************************; * >>>>>>>>>>>>>>>>>>>>>>>> Change the parameters here <<<<<<<<<<<<<<<<<<<*; * Sheetname,dbms,libref,compress,getnames,mixed,scantext,usedate,scantime *; * For access getnames becomes scanmemo *; *****************************************************************************; %imploop("c:\sastest\accessfiles\testpass.mdb", /* name of the mdb file */ access, /* access engine to use */ work, /* name of the libref to import to */ no, /* compress spaces out of table names */ yes, /* scan memo */ 1024, /* memo size */ no, /* Use date to read dates only */ no, /* Scan time */ append, /* append on to a master file, append or no */ work, /* libref for the master table */ master, /* name of the master table */ no, /* delete the master table at beginning, delete or no */ Space9); /* password on the access table */ run; quit; libname xcl clear; ods listing;