Help with sas macro to import multiple excel spreadsheets

Reply
New Contributor
Posts: 4

Help with sas macro to import multiple excel spreadsheets

Hi All,


I have been trying to use this code to export an excel file with multiple sheets in it, but keep getting this error listed below. I got the code from this source:http://www2.sas.com/proceedings/sugi31/034-31.pdf


Sample Code 1: Processing selected Excel files; <br></p><p>%let dir=/folders/myfolders;       <br></p><p>%macro ReadXls(inf);<br></p><p>libname xl xlsx "&amp;dir./&amp;inf";                     / STEP 1 /</p><p><br></p><p>proc sql noprint;                                 / STEP 2 /                     </p><p>        create table sheetname as <br></p><p>         select memname as sheetname </p><p>          from sashelp.vstabvw</p><p>          where libname="xl"; </p><p>         select count(DISTINCT sheetname) into :cnt_sht </p><p>           from sheetname; </p><p>         select DISTINCT sheetname into :sheet1 - :sheet%left(&amp;cnt_sht) </p><p>           from sheetname; </p><p>quit;</p><p>libname xlsx clear;                             / STEP 3 /<br></p><p>%do i=1 %to &amp;cnt_sht; <br></p><p> proc import datafile="&amp;dir.\&amp;inf"              / STEP 4 /<br></p><p>       out=sheet&amp;i replace; </p><p>       sheet="&amp;&amp;sheet&amp;i"; </p><p>       getnames=yes; </p><p>       mixed=yes; </p><p> run; </p><p>proc append base=master data=sheet&amp;i force;     / STEP 5 /</p><p>   run; </p><p>%end; </p><p>%mend ReadXls; </p><p>%ReadXls (book.xlsx) </p><p><br></p><p>ERROR</p><p><br></p><p class="sasSource"> 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;</p><p class="sasSource"> SYMBOLGEN: Macro variable SASWSTEMP resolves to /folders/myfolders/.images/d31a6e3a-de55-4107-a35c-8b0fd7d36f5c</p><p class="sasSource"> SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.</p><p class="sasSource"> SYMBOLGEN: Macro variable GRAPHINIT resolves to  </p><p class="sasSource"> 55  </p><p class="sasSource"> 56 %let dir=/folders/myfolders;</p><p class="sasSource"> 57 %macro ReadXls(inf);</p><p class="sasSource"> 58 libname xl xlsx "&amp;dir./&amp;inf"; / STEP 1 /</p><p class="sasSource"> 59  </p><p class="sasSource"> 60 proc sql noprint; / STEP 2 /</p><p class="sasSource"> 61 create table sheetname as</p><p class="sasSource"> 62 select memname as sheetname</p><p class="sasSource"> 63 from sashelp.vstabvw</p><p class="sasSource"> 64 where libname="xl";</p><p class="sasSource"> 65 select count(DISTINCT sheetname) into :cnt_sht</p><p class="sasSource"> 66 from sheetname;</p><p class="sasSource"> 67 select DISTINCT sheetname into :sheet1 - :sheet%left(&amp;cnt_sht)</p><p class="sasSource"> 68 from sheetname;</p><p class="sasSource"> 69 quit;</p><p class="sasSource"> 70 libname xlsx clear; / STEP 3 /</p><p class="sasSource"> 71 %do i=1 %to &amp;cnt_sht;</p><p class="sasSource"> 72 proc import datafile="&amp;dir.\&amp;inf" / STEP 4 /</p><p class="sasSource"> 73 out=sheet&amp;i replace;</p><p class="sasSource"> 74 sheet="&amp;&amp;sheet&amp;i";</p><p class="sasSource"> 75 getnames=yes;</p><p class="sasSource"> 76 mixed=yes;</p><p class="sasSource"> 77 run;</p><p class="sasSource"> 78 proc append base=master data=sheet&amp;i force; / STEP 5 */

 79 run;

 80 %end;

 81 %mend ReadXls;

 82 %ReadXls (book.xlsx)

 SYMBOLGEN: Macro variable DIR resolves to /folders/myfolders

 SYMBOLGEN: Macro variable INF resolves to book.xlsx

 NOTE: Libref XL was successfully assigned as follows: 

  Engine: XLSX 

  Physical Name: /folders/myfolders/book.xlsx

 NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

 NOTE: Table WORK.SHEETNAME created, with 0 rows and 1 columns.

 SYMBOLGEN: Macro variable CNT_SHT resolves to 0

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable TARGET resolves to  

 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable TARGET resolves to  

 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable I resolves to 1

 SYMBOLGEN: Macro variable I resolves to 1

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable I resolves to 1

 SYMBOLGEN: Macro variable I resolves to 1

 SYMBOLGEN: Macro variable TEXT resolves to 0

 SYMBOLGEN: Macro variable I resolves to 1

 WARNING: INTO Clause :sheet1 through :sheet0 does not specify a valid sequence of macro variables.

 NOTE: No rows were selected.

 NOTE: PROCEDURE SQL used (Total process time):

  real time 0.09 seconds

  cpu time 0.07 seconds

   

 WARNING: Libref XLSX is not assigned.

 SYMBOLGEN: Macro variable CNT_SHT resolves to 0

 83  

 84 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

 SYMBOLGEN: Macro variable GRAPHTERM resolves to  

 96  

Super User
Super User
Posts: 6,502

Re: Help with sas macro to import multiple excel spreadsheets

Why are you using a macro to do something that is easy to do without one?

libname in xlsx 'book.xlsx';

proc copy inlib=in outlib=work; run;

New Contributor
Posts: 4

Re: Help with sas macro to import multiple excel spreadsheets

Hi Tom,

Thank you very much Tom. I found that macro on my google search. Once the workbooks are imported, is there a way to work with these files despite their data set names?

For instance, the tables I am working with have sheet names like: CLAB Table 2_07.27.2015. I cannot read this name as is on a data step. Is there a way to work around that without having to physically rename each worksheet?

Once again, thank you very much for your time

Carla

Super User
Super User
Posts: 6,502

Re: Help with sas macro to import multiple excel spreadsheets

If you set the option VALIDVARNAME=ANY then you should be able to reference them using a name literal.

options validvarname=any ;

libname in xlsx 'book.xlsx';

data mydata ;

  set in.'CLAB Table 2_07.27.2015'n ;

run;

New Contributor
Posts: 4

Re: Help with sas macro to import multiple excel spreadsheets

Thank you Tom,

The code works on SAS Enterprise Guide 7.1, but not on SAS 9.4 for Windows. Any ideas why, please see error message below:

NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.


NOTE: SAS (r) Proprietary Software 9.4 (TS1M2)


      Licensed to CA DEPARTMENT OF TECHNOLOGY, Site 70082650.


NOTE: This session is executing on the X64_7PRO  platform.


NOTE: Updated analytical products:


      SAS/STAT 13.2


      SAS/ETS 13.2


      SAS/OR 13.2


      SAS/IML 13.2


      SAS/QC 13.2


NOTE: Additional host information:


X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation


NOTE: SAS initialization used:


      real time           0.78 seconds

      cpu time            0.62 seconds


NOTE: Unable to open SASUSER.REGSTRY. WORK.REGSTRY will be opened instead.


NOTE: All registry changes will be lost at the end of the session.


WARNING: Unable to copy SASUSER registry to WORK registry. Because of this,

WARNING: you will not see registry customizations during this session.


NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.


NOTE: All profile changes will be lost at the end of the session.


NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of this


NOTE: session.


NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.


NOTE: All profile changes will be lost at the end of the session.



1    options validvarname=any;


2


3    libname in xlsx 'U:\Public Reports\2014data(Jan-Dec)\2014 Data Final Tables.HAI Report for


3  ! Lynn J\HAI_tables_sent_by_LJ_to_JeanIacino\SSI\SSI Tables 1-12 2014 7.27.15.xlsx';


NOTE: Libref IN was successfully assigned as follows:


      Engine:        XLSX


      Physical Name: U:\Public Reports\2014data(Jan-Dec)\2014 Data Final Tables.HAI Report for


      Lynn J\HAI_tables_sent_by_LJ_to_JeanIacino\SSI\SSI Tables 1-12 2014 7.27.15.xlsx


4    proc copy inlib=in outlib=work; run;


NOTE: Writing HTML Body file: sashtml.htm



NOTE: Copying IN.'SSI TABLE 1 AAA 2014 7.27.15'n to WORK.'SSI TABLE 1 AAA 2014 7.27.15'n


      (memtype=DATA).


NOTE: BUFSIZE is not cloned when copying across different engines.


      System Option for BUFSIZE was used.


ERROR: The value 'SSI TABLE 1 AAA 2014 7.27.15'n is not a valid SAS name.


ERROR: File WORK.'SSI TABLE 1 AAA 2014 7.27.15'n.DATA has not been saved because copy could not


       be completed.


      real time           0.00 seconds


      cpu time            0.01 seconds

Super User
Super User
Posts: 6,502

Re: Help with sas macro to import multiple excel spreadsheets

You need to also use the VALIDMEMNAME=EXTEND option if you want to also use those strange names as the names of SAS datasets.

New Contributor
Posts: 4

Re: Help with sas macro to import multiple excel spreadsheets

Thank you very much for all your input. I hope we can stay connected.

Best,

Carla

Ask a Question
Discussion stats
  • 6 replies
  • 340 views
  • 0 likes
  • 2 in conversation