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.
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;
first of all this
sheet = sheet1;
should be
sheet = "sheet1";
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.
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
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;
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!
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.