Hello,
I am trying to proc import an Excel file (xlsx), but I am not able to properly import the variable names.
%let sname1='hyfi prm cnts'n;
PROC IMPORT
DATAFILE='/.../Inforce_N_Hyfi_count02JUL21.xlsx'
OUT=test
DBMS=xlsx
REPLACE;
SHEET="&sname1.";
range="&sname1.$A5:S500";
GETNAMES=YES;
RUN;
The range option is not working
Excel is not going like that letter N in the RANGE setting. You don't have to set the end of the range, just the upper left corner.
PROC IMPORT DATAFILE='/.../Inforce_N_Hyfi_count02JUL21.xlsx'
DBMS=xlsx
OUT=test REPLACE
;
RANGE="'hyfi prm cnts'$A5:";
GETNAMES=YES;
RUN;
What happens when you omit the SHEET= statement?
My personal solution would be to save the data to a delimited (comma, tab, whatever) text file and read that with a data step.
%let sname1='hyfi prm cnts'n;
PROC IMPORT
DATAFILE='/.../Inforce_N_Hyfi_count02JUL21.xlsx'
OUT=test
DBMS=xlsx
REPLACE;
/* SHEET="&sname1.";*/
range="&sname1.$A5:S500";
GETNAMES=YES;
RUN;
Couldn't find sheet in spreadsheet
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
Excel is not going like that letter N in the RANGE setting. You don't have to set the end of the range, just the upper left corner.
PROC IMPORT DATAFILE='/.../Inforce_N_Hyfi_count02JUL21.xlsx'
DBMS=xlsx
OUT=test REPLACE
;
RANGE="'hyfi prm cnts'$A5:";
GETNAMES=YES;
RUN;
On second thought, I see that you try to use a SAS name literal as a sheet name (something you would do if you used LIBNAME XLSX and the sheet as a pseudo-dataset).
But so, the RANGE statement will resolve to
range="'hyfi prm cnts'n$A5:S500";
and I have doubts that this will work unless the sheet is actually named 'hyfi prm cnts'n (including the quotes and the trailing n).
Syntax such as the following should work:
%let sname=sheet1$;
proc import datafile = C:\myfiles\Excel Files\class.xlsx"
out = test
dbms = xlsx replace;
range = "&sname.A5:E30";
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.