I am importing )Proc Import) ~50 spreadsheets to SAS. There are couple of challanges.
(1) variable names are in Row 9 and data begins in row 10. I tried to use datarow= option with getnames=. My variable names appear as first row instead of header in SAS. If I use datarow=10 and use getnames=yes, I get the data without any variable names
(2) Data has footnotes. How to delete them?
Since I am importing multiple workbooks, the row that footnote appears varies. E.g One.xlx has footnote in row 15 and two.xls has footnote in row 30. But the variable name and the first row of data alwys are in row 9 and 10. Below is a snap shot of my spreadsheet
Heading1
Heading2
Heading3
AK
V1 V2 V3 V4
1
2
3
4
Footnote
I would like to use a macro instead of using 50 proc import statement. Any suggestions would help.
Thanks.
50 workbooks(xls files) or 50 sheets in a single workbook. Either way I think NAMED RANGES will be the key. How you define them is another question, manually or automated.
Post example workbook(s).
50 workbooks (xls files)
WorkBook1
Heading1
Heading2
Heading3
AK
Name Age Sex
q
w
e
Foortnote
WorkBook2
Heading1
Heading2
Heading3
AL
Name Age Sex
a
s
d
f
g
h
Footnote
If you always have the same variables in each sheet, then the task can be simplified. See if something like the following might work (Note: the range statement includes the row with the variable names):
PROC IMPORT OUT= WORK.TESTxl
DATAFILE= "C:\art\testxl.xls"
DBMS=EXCEL REPLACE;
GETNAMES=YES;
range="A7:D9999";
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
data testxl;
set testxl;
if nmiss(of V1-V4) then stop;
run;
Thanks. But it didn't work. With the code I get additional columns other than the variable name I need and there is no data.
I used if nmiss(of name age sex) then stop; Since my variables are not named as v1-v4. I used them for e.g. purposed. But the variables in all the worksheets are same.
I partly solved the problem with below code.
Proc import out=x
datafile='xxxx.xls'
dbms=xlsreplace;
getname=yes;
namerow=9; /*this helped to use the values as variables names*/
datarow=10;
Run;
With this code my data looks
Name Age Sex
a
s
d
Footnote
I am yet to figure out how to remove the footnote.
Also since there are several datasets that I am importing and the row with footnote is not consistent I am not sure how to put them in macro.
Thanks.
Doe the footnote always only take up one cell? And, are name and sex, or name and age always present? If so, in the datastep you might be able to use if nmiss(name sex) ge 1 then stop;
Yes, Footnote always takes up one cell.
Yes, Name, age, and sex all are always present.
Still doesn't work. If I use
if nmiss(name sex age) ge 1 then stop;
I get Error 388-185: expecting an arithmetic operator
If I use
if nmiss(name) ge 1 then stop; or if nmiss(name, age, sex) ge 1 then stop;
I get NOTE: variable name is uninitialized and no data
I am replacing the variable names, I am not sure that is causing these errors or something else that I am not seeing.
Thanks once again.
Try the cmiss function. e.g.,
data have;
input (name sex) ($) age;
cards;
John M 20
Mary F 25
Harry M 40
Footer
;
data want;
set have;
if cmiss(name,sex,age) gt 1 then stop;
run;
Thanks. Still did not work. I find a way around. I used the namerow and datarow options in Proc Import. The data had footnote. Then in dataset I retained obs for the id variables that were not missing. this helped to get the desired result.
thanks a lot again for all who responded
Please show the code you used. That "should" have worked.
Sure. Below is my code.
Proc import out=x;
datafile='xxx.xls'
dbms=excel replace;
getname=yes;
namerow=9;
datarow=10;
Run;
Data x;
Set x;
Where variable1 ne ' ';
Run;
For my case footnotes in all the 50 spreadsheets starts in column2 instead of 1. If my footnotes were to start in column1 I was planning to see if I could use 'nodigit' function since my column1 has numbers.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.