Macro function which can read Excel spreadsheets with multiple tab

Reply
Occasional Contributor
Posts: 13

Macro function which can read Excel spreadsheets with multiple tab

Hi All,

I want to create macro which can able to create the dataset of each tabs in the Excel spreadsheets.

Super Contributor
Posts: 297

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

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;

Super User
Posts: 17,881

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

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;

Ask a Question
Discussion stats
  • 2 replies
  • 260 views
  • 0 likes
  • 3 in conversation