Proc Import statements not working: range, getnames

[ayin]

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?

[Tom]

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

[Reeza · Accepted solution]

Two possibly relevant sections from the documentation.

https://support.sas.com/documentation/cdl/en/acpcref/69731/HTML/default/viewer.htm#n0msy4hy1so0ren1a...

Documentation:

You can use RANGE= to specify the row number where PROC IMPORT starts to read data. Set the end point to “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.

If GETNAMES=YES is set, the first row of data in the range is used for the column names, and the data starts from the second row in the range. If GETNAMES=NO is set, the data starts from the first row and column names are generated by the IMPORT procedure.

You can use the DATASETS procedure to list the SAS data set names that are mapped to the range-names. If the displayed range-name contains single quotation marks, keep the single quotation marks as part of the range-name to access the sheet, and enclose the entire name in double quotation marks. 

The following examples demonstrate the use of RANGE=.

• To retrieve data from the worksheet for two separate sheet names, '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$'";
   

  Note: If you want to read data from two ranges in the same Excel workbook file, two PROC IMPORT steps must be submitted.
• To represent cells within Column C, Row 2, and Column F, Row 12: ‘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.
• When data is imported from an Excel file, a sheet name that is appended with a $ character is treated as a range name. The range name refers to the whole sheet; RANGE="summary$a4:b20" or RANGE='summary$a4:b20’n
• If the range-name is available, it is recommended that you use RANGE= option without the SHEET= option for the IMPORT procedure. To use the absolute range address, it is strongly recommended that you use the full range address with quotation marks. For example, specify RANGE='sheet_name$A1:C7'n;. See also the SHEET=sheet-name.