import multiple Excel sheets into SAS

Alexxxxxxx — Sun, 07 Apr 2019 21:17:25 GMT

Dear all,

I am trying to import multiple .excel sheets by following the code,

%macro CountryN(country=,range=A1:);

proc import datafile = "I:\DATASTREAM\DATASTREAM_TOTAL.xlsx"
OUT=DATASTREAM_&country. DBMS=XLSX replace;
Sheet='&country.';
range=A1:'&range.';
GETNAMES=YES;
RUN;


%MEND CountryN;

%CountryN(country=Japan,range=V14595)
%CountryN(country=Netherlands,range=V1446)
%CountryN(country=New_Zealand,range=V960)
%CountryN(country=Norway,range=V1590)
%CountryN(country=Singapore,range=V2970)
%CountryN(country=Spain,range=V1665)
%CountryN(country=Sweden,range=V3663)
run;

however, I get

4193  %macro CountryN(country=,range=);
4194
4195  proc import datafile = "I:\DATASTREAM\DATASTREAM_TOTAL.xlsx"
4196  OUT=DATASTREAM_&country. DBMS=XLSX replace;
4197  Sheet='&country.';
4198  RANGE='A1:&range.';
4199  GETNAMES=YES;
4200  RUN;
4201
4202
4203  %MEND CountryN;
4204
4205  %CountryN(country=Japan,range=V14595)

ERROR: Couldn't find sheet in spreadsheet

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

4206  %CountryN(country=Netherlands,range=V1446)



ERROR: Couldn't find sheet in spreadsheet

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

4207
4208  run;

Could you please give me some suggestions about this?

thanks in advance.

Re: import multiple Excel sheets into SAS

ChrisNZ — Sun, 07 Apr 2019 21:39:25 GMT

Use double quotes.

Macro variables are not resolved inside single quotes.

Re: import multiple Excel sheets into SAS

SASKiwi — Sun, 07 Apr 2019 21:32:18 GMT

As well as @ChrisNZ's suggestion, try running this without the RANGE statement. Same error?

Re: import multiple Excel sheets into SAS

Ksharp — Mon, 08 Apr 2019 14:05:38 GMT

Try:

proc import datafile = "I:\DATASTREAM\DATASTREAM_TOTAL.xlsx"
OUT=DATASTREAM_&country. DBMS=XLSX replace;

range="&country.$A1:&range.";

GETNAMES=YES;
RUN;

topic Re: import multiple Excel sheets into SAS in SAS Programming

import multiple Excel sheets into SAS

Re: import multiple Excel sheets into SAS

Re: import multiple Excel sheets into SAS

Re: import multiple Excel sheets into SAS