topic Re: proc import xlsx with variable name at obs=5 dand data at obs=6 in SAS Enterprise Guide

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

alepage — Mon, 20 Sep 2021 18:12:15 GMT

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

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

Kurt_Bremser — Mon, 20 Sep 2021 18:31:56 GMT

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.

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

alepage — Mon, 20 Sep 2021 19:11:05 GMT

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

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

Tom — Mon, 20 Sep 2021 19:16:42 GMT

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;

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

jebjur — Mon, 20 Sep 2021 19:21:12 GMT

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;

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

Kurt_Bremser — Mon, 20 Sep 2021 19:37:52 GMT

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