BookmarkSubscribeRSS Feed
DJChavda
Obsidian | Level 7

Hi All,

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

2 REPLIES 2
Scott_Mitchell
Quartz | Level 8

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;

Reeza
Super User

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 638 views
  • 0 likes
  • 3 in conversation