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.Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.