topic Re: How to read a range of cells from Excel ? in SAS Programming

How to read a range of cells from Excel ?

Kumar6 — Wed, 08 Jun 2022 13:42:47 GMT

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.

Re: How to read a range of cells from Excel ?

Kurt_Bremser — Wed, 08 Jun 2022 13:55:05 GMT

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.

Re: How to read a range of cells from Excel ?

Tom — Wed, 08 Jun 2022 14:03:25 GMT

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;

Re: How to read a range of cells from Excel ?

Kumar6 — Wed, 08 Jun 2022 15:05:19 GMT

It worked. Thank You.