How to read a range of cells from Excel ?

[Kumar6]

Hi All, 

I am trying to get the data from an excel sheet into the sas dataset. I need the data in excel sheet starting from row 6 and row 5 should be a column names. When I tried to get this with below code its giving me an error( Statement is not valid or it is used out of proper order) for the last 4 statements(mixed,  namerow, startrow, endrow). Can anyone please suggest me any other alternate way on this.

proc import datafile = "C:\CR-6365.xlsx"
DBMS = xlsx
OUT = a1
REPLACE;;
sheet = "PQCs For CRAs";
GETNAMES =Yes;
Mixed=YES;
namerow=5;
startrow=6;
endrow=16;
quit;

Thanks,

Kumar.

[Kurt_Bremser · Accepted solution]

Maxim 1: Read the Documentation.

In this case, the specific page for Microsoft Workbook Files 

There are no NAMEROW, STARTROW or ENDROW statements for DBMS=XLSX (only for DBMS=XLS).

Instead, use a RANGE= statement to direct SAS to the correct part of the spreadsheet. The specific part for the RANGE statement is here.

[Tom]

Sounds like you have the same issue as this other question.

Just tell it where to start.  

proc import
   datafile = "C:\CR-6365.xlsx"
  DBMS = xlsx
  OUT = a1 REPLACE
;
  range =  "PQCs For CRAs$A5:";
run;

[Kumar6]

It worked. Thank You.