topic Re: Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop in SAS Programming

Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop

ImASasMan — Wed, 19 Feb 2020 14:34:36 GMT

Good morning!

I have been testing out importing multiple sheets from 1 excel book by using a %Do loop within a macro. I want to import the sheets based upon their naming conventions in this case 201701, 201702, 201703,..... 201712. The issue that I am having is that for sheets 201701-201709, SAS is telling me that it cannot find the sheets. It does however import for sheets 201710 - 201712. Here is the code that I am using:

%macro importy1;

%if &year1. >2018 %THEN %DO;
%do i = 1 %to 4;
Proc Import out= WORK.RS_&Year1._Q&i DATAFILE= Y1RS DBMS=xlsx REPLACE;
SHEET="&Year1. Q&i";
GETNAMES=YES;
RUN;
%END;
%END;
%else %if &year1.<= 2018 %then %do;
%do i = 1 %to 12;
%if i < 10 %then %do;
Proc Import out= WORK.RS_&Year1.0&i DATAFILE= Y1RS DBMS=xlsx REPLACE;
SHEET="&Year1.0&i";
GETNAMES=YES;
RUN;
%END
%ELSE %IF i > 9 %then %do;
Proc Import out= WORK.RS_&Year1.&i DATAFILE= Y1RS DBMS=xlsx REPLACE;
SHEET="&Year1.&i";
GETNAMES=YES;
RUN;
%end
%end;
%end;

%MEND IMPORTy1;

%importy1;

From what I can tell, the issue is that the 0 in the <10 statement is not being interpreted correctly, however I do not know how to correct this. If you could provide any insight on how to remedy this it would be greatly appreciated.

Thanks!

Re: Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop

PaigeMiller — Wed, 19 Feb 2020 14:43:25 GMT

The issue that I am having is that for sheets 201701-201709, SAS is telling me that it cannot find the sheets.

Then they are not there under that specific name in Excel, or the macro variables are not resolving as you expect. Perhaps the names in Excel are typed differently, and perhaps have spaces before or after the calendar date.

You might also want to turn on

options mprint;

and run your code again and look in the log to see if the macro variables are begin created and resolved as you want them to be.

Re: Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop

Kurt_Bremser — Wed, 19 Feb 2020 14:48:26 GMT

You have to correctly reference your macro variable i. i is just text, &i is a reference to a macro variable:

%do i = 1 %to 12;
  %if &i < 10 %then %do;
    proc import out=WORK.RS_&Year1.0&i DATAFILE=Y1RS dbms=xlsx replace;
    sheet="&Year1.0&i";
    getnames=yes;
    run;
  %end;
  %else %do;
    proc import out=WORK.RS_&Year1.&i DATAFILE=Y1RS dbms=xlsx replace;
    sheet="&Year1.&i";
    getnames=yes;
    run;
  %end;
%end;

Re: Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop

Tom — Wed, 19 Feb 2020 14:59:52 GMT

The letter i is never going to be less that the digit 1. It is always going to be larger than the digit 9.

Why do you have that %IF at all?

%if &year1.<= 2018 %then %do;
  %do month = 1 %to 12;
    %let sheet=&year1.%sysfunc(putn(&month,z2));
proc import out= WORK.RS_&sheet datafile= Y1RS dbms=xlsx replace;
  sheet="&sheet";
  getnames=YES;
run;
  %end;
%end;

Re: Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop

PerryNoble — Wed, 19 Feb 2020 15:33:37 GMT

Hi !

It's always good to get to the bottom of a coding challenge but sometimes you just need an alternative solution. Depending on the version of SAS and or EG you are using you could try the LIBNAME engine XLSX. One line of code gets you all the worksheets (tabs) in a workbook. I created a dummy workbook with three tabs named 201701, 201702, 201703 with some regional salesperson data. With this simple libname, I have a SAS Lib (WRKBK) with all three tabs as datasets named 201701, 201702, 201703. (Keep in mind those names are not "good" dataset names because a sas dataset should not start with a numeric.

I realize this doesn't solve the original macro question, but if you simply need to get the data into SAS to work with, this may be a quick alternative.

SAS EG 8.1 code and lib

libname wrkbk xlsx '\\denfilera\risk-mgmt\erm\personal folders\noble\libname_excel.xlsx';
run;

Re: Import Multiple Sheets from 1 Excel Workbook Using a %Do Loop

Reeza — Wed, 19 Feb 2020 16:34:02 GMT

Adding on to @PerryNoble solution, libname + PROC COPY will copy all data sets in for you.

Worth a try at least.

libname wrkbk xlsx 'path to xlsx file';


proc copy in=wrkbk out=work;
run;