Help using Base SAS procedures

Importing excel with headings and footnotes to SAS

Reply
Contributor
Posts: 38

Importing excel with headings and footnotes to SAS

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.

    

Respected Advisor
Posts: 3,777

Importing excel with headings and footnotes to SAS

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

Contributor
Posts: 38

Importing excel with headings and footnotes to SAS

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

PROC Star
Posts: 7,363

Importing excel with headings and footnotes to SAS

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="A7Smiley Very Happy9999";

     MIXED=YES;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

data testxl;

  set testxl;

  if nmiss(of V1-V4) then stop;

run;

Contributor
Posts: 38

Importing excel with headings and footnotes to SAS

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.

PROC Star
Posts: 7,363

Importing excel with headings and footnotes to SAS

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;

Contributor
Posts: 38

Importing excel with headings and footnotes to SAS

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.

PROC Star
Posts: 7,363

Importing excel with headings and footnotes to SAS

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;

Contributor
Posts: 38

Importing excel with headings and footnotes to SAS

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

PROC Star
Posts: 7,363

Importing excel with headings and footnotes to SAS

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

Contributor
Posts: 38

Importing excel with headings and footnotes to SAS

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.

Ask a Question
Discussion stats
  • 10 replies
  • 5486 views
  • 0 likes
  • 3 in conversation