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.
Try:
proc import datafile = "I:\DATASTREAM\DATASTREAM_TOTAL.xlsx" OUT=DATASTREAM_&country. DBMS=XLSX replace; range="&country.$A1:&range."; GETNAMES=YES; RUN;
Use double quotes.
Macro variables are not resolved inside single quotes.
As well as @ChrisNZ's suggestion, try running this without the RANGE statement. Same error?
Try:
proc import datafile = "I:\DATASTREAM\DATASTREAM_TOTAL.xlsx" OUT=DATASTREAM_&country. DBMS=XLSX replace; range="&country.$A1:&range."; GETNAMES=YES; RUN;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.