BookmarkSubscribeRSS Feed
rsva
Fluorite | Level 6

Hello,

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.

    

10 REPLIES 10
data_null__
Jade | Level 19

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).

rsva
Fluorite | Level 6

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

art297
Opal | Level 21

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;

rsva
Fluorite | Level 6

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.

art297
Opal | Level 21

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;

rsva
Fluorite | Level 6

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.

art297
Opal | Level 21

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;

rsva
Fluorite | Level 6

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

art297
Opal | Level 21

Please show the code you used.  That "should" have worked.

rsva
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 7589 views
  • 0 likes
  • 3 in conversation