topic Re: PROC IMPORT in SAS Procedures

PROC IMPORT

robertrao — Wed, 20 Mar 2013 19:11:59 GMT

I want to import Excel sheets as SAS Datasets

But this one looks complex...

i am posting it down.because of space..

Regards

Re: PROC IMPORT

JohnT — Wed, 20 Mar 2013 23:16:12 GMT

Hi robertrao,

May or may not help, but perhaps you could use a LIBNAME statement.

eg: libname meref pcfiles path="c:\excel_file.xlsm";

Then you can see all the tables as SAS datasets.

Depends on your SAS version/bitness I guess.

Re: PROC IMPORT

Reeza — Thu, 21 Mar 2013 01:55:31 GMT

You've shown the data you have. I'd start off by seeing what happens with a standard proc import. If the standard proc import doesn't work, let us know which columns it doesn't work on.

Then you can work on changing your structure.

Personally, I'd keep each tab separate and then append the results at the end but its a business decision so really its what you need/feel is appropriate.

Re: PROC IMPORT

ArtC — Thu, 21 Mar 2013 02:08:39 GMT

It is a bit hard to read the image, but you can still read each sheet into a data set and then later combine the data sets.

For the information in Col A you can parse this in a data step. can we assume that there are always four distinct rows for NAME and that these should be constant for the four rows of the corresponding day values?

Re: PROC IMPORT

ArtC — Thu, 21 Mar 2013 02:39:02 GMT

Assuming that the structure is as implied then the DATA step that will parse that first column could look something like this:

data sheet;

length namex $15;

input namex $ something $ day1 day2 day3;

datalines;

Frederick xxx 1 2 3

mrn1 xyy 4 5 6

25may12 xzz 7 8 9

a a 9 6 4

samantha yyy 4 5 6

mrn2 yxx 9 8 7

15feb12 yzz 12 12 12

a a 9 6 4

run;

data want(keep=name mrn date day:);

length name mrn $8 date 8;

format date date9.;

retain name mrn date;

* Read ahead to capture the 3 name field values;

retain pt 0;

do cnt=1 to 3;

point=pt+cnt;

set sheet(keep=namex) point=point;

if cnt= 1 then name=namex;

else if cnt=2 then mrn=namex;

else if cnt=3 then date = input(namex,date9.);

end;

* Read in the DAY fields;

do cnt = 1 to 4;

pt = pt+1;

set sheet(keep=day:) point=pt nobs=nobs;

output want;

end;

if pt=nobs then stop;

run;