Have an excel sheet looks like:
0 1 2 ...
Col_A Col_B Col_C ...
1 A ...
C word ...
I want to use row 2 as variable/column names (A2:CQ2);
data is from the range: A3:CQ77.
Codes I was using to import the sheet:
proc import datafile = "&_filepath&_fileprefix&_filesuffix"
out = work.RefTable
replace
dbms= xlsx;
sheet= 'Sheet Test'n;
range="A2:CQ77";
getnames=yes;
run;
However it still picked up row 1 as column names, and still picked up all the data outside the range specified (A2:CQ77).
How to fix it?
“0”
, and the code then determines the last row and last column. Specify RANGE="Sheetname$A#:0"; where # is the first data row. Thus, RANGE="sheet1$A3:0";
starts to read the data at row 3. If you use RANGE= for this purpose, do not specify the DATAROW= statement.'My#Test$'
and ' CustomerOrders'
, use one RANGE= statement in each PROC IMPORT step. Only one RANGE= statement is used in a PROC IMPORT step. Note that the name must be enclosed in quotation marks: RANGE="'My#Test$'";
‘C2:F12’
the colon separates the values for upper left (UL) and lower right (LR) of the range. If this statement is not specified, the IMPORT procedure reads the entire worksheet as a range.RANGE="summary$a4:b20"
or RANGE='summary$a4:b20’n
RANGE='sheet_name$A1:C7'n;
. See also the SHEET=sheet-name.I think you might need to put the sheetname into the range?
data have ;
input (A B C) ($);
cards;
Col_A Col_B Col_C ...
1 A ...
. C word
;
%let path=%sysfunc(pathname(work));
libname out xlsx "&path\sample.xlsx";
data out.test; set have; run;
libname out clear ;
proc import datafile="&path\sample.xlsx"
out=test replace
dbms=xlsx
;
range='test$::A2:C4';
getnames=yes;
run;
proc print; run;
Obs Col_A Col_B Col_C 1 1 A ... 2 C word
“0”
, and the code then determines the last row and last column. Specify RANGE="Sheetname$A#:0"; where # is the first data row. Thus, RANGE="sheet1$A3:0";
starts to read the data at row 3. If you use RANGE= for this purpose, do not specify the DATAROW= statement.'My#Test$'
and ' CustomerOrders'
, use one RANGE= statement in each PROC IMPORT step. Only one RANGE= statement is used in a PROC IMPORT step. Note that the name must be enclosed in quotation marks: RANGE="'My#Test$'";
‘C2:F12’
the colon separates the values for upper left (UL) and lower right (LR) of the range. If this statement is not specified, the IMPORT procedure reads the entire worksheet as a range.RANGE="summary$a4:b20"
or RANGE='summary$a4:b20’n
RANGE='sheet_name$A1:C7'n;
. See also the SHEET=sheet-name.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.