proc import xlsx with variable name at obs=5 dand data at obs=6

[alepage]

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

[Kurt_Bremser]

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.

[alepage]

%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.

[Tom · Accepted solution]

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;

[Kurt_Bremser]

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).

[jebjur]

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;