Hi All,
I want to create macro which can able to create the dataset of each tabs in the Excel spreadsheets.
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;
No macros needed:
http://listserv.uga.edu/cgi-bin/wa?A2=ind1211e&L=sas-l&F=&S=&P=8001
Libname in 'C:\Park\bhupindertest.xlsx';
Proc contents data = in._all_ out = a noprint;;
run;
Data _null_ ;
set a (keep = memname) end = eof;;
name = compress(memname, '$');
name = translate(trim(name) , '_', ' ');
str =compbl( "Data " || name || "; set in.'" || memname ||"'n;" );
Call Execute (str);
if eof then do;
str = 'run;';
call execute (str);
end;
run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.