topic Importing excel with headings and footnotes to SAS in SAS Procedures

Importing excel with headings and footnotes to SAS

rsva — Wed, 27 Jul 2011 16:20:16 GMT

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

V1 V2 V3 V4

Footnote

I would like to use a macro instead of using 50 proc import statement. Any suggestions would help.

Thanks.

Importing excel with headings and footnotes to SAS

data_null__ — Wed, 27 Jul 2011 16:33:41 GMT

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

Importing excel with headings and footnotes to SAS

art297 — Wed, 27 Jul 2011 17:08:46 GMT

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;

Importing excel with headings and footnotes to SAS

rsva — Wed, 27 Jul 2011 17:16:00 GMT

50 workbooks (xls files)

WorkBook1

Heading1

Heading2

Heading3

Name Age Sex

Foortnote

WorkBook2

Heading1

Heading2

Heading3

Name Age Sex

Footnote

Importing excel with headings and footnotes to SAS

rsva — Wed, 27 Jul 2011 17:30:42 GMT

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

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.

Importing excel with headings and footnotes to SAS

art297 — Wed, 27 Jul 2011 18:35:13 GMT

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;

Importing excel with headings and footnotes to SAS

rsva — Wed, 27 Jul 2011 19:35:02 GMT

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.

Importing excel with headings and footnotes to SAS

art297 — Wed, 27 Jul 2011 19:47:13 GMT

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;

Importing excel with headings and footnotes to SAS

rsva — Thu, 28 Jul 2011 19:46:38 GMT

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

Importing excel with headings and footnotes to SAS

art297 — Thu, 28 Jul 2011 22:46:03 GMT

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

Importing excel with headings and footnotes to SAS

rsva — Fri, 29 Jul 2011 12:21:57 GMT

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.