proc import xlsx secific row

[ubshams]

Hi Everyone,

   I am trying to import data from an excel file that looks similar to the below screenshot and attached file. 

Data starts in row 13 (which I was able to get), and column names to be from column 5 (not row 1) which I was not able to get.

Current code:

proc import datafile="/foldername/sample1.xlsx"

dbms = xlsx

out = outfile.sample1 replace;

getnames= no;

datarow= 13;

namerow=5;

sheet = sheet1;

run;

Current Error:

27 dbms = xlsx

28 out = out.sample1 replace;

NOTE: The previous statement has been deleted.

29 getnames= no;

30 datarow= 13;

31 namerow=5;

_______

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

32 sheet = sheet1;

33 run;

1. How can i fix this error and get column names in SAS to be  from row 5 of the excel file?

2. Row 6 contains some description about the vriables, how can I convert that into a "Label" in SAS during or after

importing?

Thanks!

Attached my sample data below.

[VDD]

first of all this 

sheet = sheet1;

should be

sheet = "sheet1";

[SASKiwi]

You can read the documentation on PROC IMPORT of Excel spreadsheets here: https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n0msy4hy1so0ren1acm90iijxn8j.htm&docset...

Please note the options that are available to you if you use DBMS = XLSX. They are GETNAMES, RANGE and SHEET only. DATAROW and NAMEROW are not valid for importing spreadsheets at all. I'm guessing you picked these up from DBMS = CSV or similar.

[TomKari]

Excel data unfortunately is very susceptible to being moved around, cell characteristics changing, etc.

My solution is to always import the entire worksheet, and then using base code edit both the cells that I expect to contain data, to ensure the contents are reasonable, and to ensure there's nothing I don't expect in the other cells. Otherwise, you're likely to keep processing worksheets and producing results, even when the cells no longer line up the way they should.

Tom

[Patrick · Accepted solution]

@ubshams 

In case you don't need to implement a repetitive process but you just need to read this specific Excel sheet into SAS: I wouldn't bother to write code which can read this messy structure but I would modify the structure so that it's easy to read.

What you could do:

1. copy the sheet

2. get rid of all unwanted rows; have the column headers in row 1 and the data starting in row 2

3. store the sheet as .csv

4. eventually use the EG import wizard to generate the SAS code for reading the .csv

5. modify the generated code as you see fit (i.e. add a label statement, modify formats to whatever you want).

Here the EG generated code reading the attached .csv created from your sample Excel data.

I've already added a label statement as well.

DATA WORK.sample;
  LENGTH
    m2                 8
    xyz1               8
    xyz2               8
    xyz3               8
  ;
  FORMAT
    m2               DATE9.
    xyz1             BEST6.
    xyz2             BEST6.
    xyz3             BEST4.
  ;
  INFORMAT
    m2               DATE9.
    xyz1             BEST6.
    xyz2             BEST6.
    xyz3             BEST4.
  ;
  label 
    m2='some label'
  ;
  INFILE '/foldername/sample1_csv.csv'
    DLM=';'
    MISSOVER
    DSD;
  INPUT
    m2               : ?? DATE9.
    xyz1             : ?? COMMA6.
    xyz2             : ?? COMMA6.
    xyz3             : ?? COMMA4.
  ;
run;