Help using Base SAS procedures

PROC IMPORT

Reply
Super Contributor
Posts: 1,040

PROC IMPORT

I want to import Excel sheets as SAS Datasets

But this one looks complex...

i am posting it down.because of space..

Regards

Frequent Contributor
Posts: 75

Re: PROC IMPORT

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.

Super User
Posts: 17,829

Re: PROC IMPORT

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.

Valued Guide
Posts: 632

Re: PROC IMPORT

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?

Valued Guide
Posts: 632

Re: PROC IMPORT

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 daySmiley Happy;

   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=daySmiley Happy point=pt nobs=nobs;

      output want;

   end;

   if pt=nobs then stop;

   run;

Ask a Question
Discussion stats
  • 4 replies
  • 303 views
  • 3 likes
  • 4 in conversation