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

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.

 

Capture.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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;

 

View solution in original post

4 REPLIES 4
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

first of all this 

sheet = sheet1;

should be

sheet = "sheet1";

 

 

SASKiwi
PROC Star

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
Onyx | Level 15

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
Opal | Level 21

@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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2988 views
  • 4 likes
  • 5 in conversation