import multiple Excel sheets into SAS

[Alexxxxxxx]

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.

[ChrisNZ]

Use double quotes.

Macro variables are not resolved inside single quotes.

[SASKiwi]

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

[Ksharp · Accepted solution]

Try:

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

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

GETNAMES=YES;
RUN;