BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
Two possibly relevant sections from the documentation.
 
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.

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
Super User
Two possibly relevant sections from the documentation.
 
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 5959 views
  • 1 like
  • 3 in conversation