Help using Base SAS procedures

Execel importe problem

Reply
Occasional Contributor
Posts: 15

Execel importe problem

I have problem to import data from Excel to SAS dataset. When the Excel file has the observation is (as below) in row, I just use the usual import wizard, then done with the sas code.

 

ProjNumClaimAdvRareLastClaim
14101
21910.720
31200.37211
4900.0540


PROC IMPORT OUT= WORK.SME

     DATAFILE= "E:\project\file.xls"

DBMS=EXCEL REPLACE;

     RANGE="SME$";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

If I have the observation is (as below) in column, the import wizard will import it but in wrong variables, what should i do?

ProjNum1234
ClaimNum419129
PreAdv1100
PreAdjR00.720.37210.054
LasClaim1010

Thank you

PROC Star
Posts: 7,363

Execel importe problem

You could always transpose it, but I'd be interested in seeing where the problem originates.  Can you attach a copy of the spreadsheet to your post?

Frequent Contributor
Posts: 90

Re: Execel importe problem

Hi,

Try this below code.I got the output without any errors,

PROC IMPORT OUT= SME

            DATAFILE= "E:\project\file.xls"

            DBMS=EXCEL REPLACE;

     GETNAMES=YES;

     MIXED=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

Thanks

PROC Star
Posts: 7,363

Re: Execel importe problem

Okay, after re-reading your post, I presume that the data are simply exactly as shown in the spreadsheet.  If so, I stick with my original suggestion, but it takes a couple of steps.  Try something like:

PROC IMPORT OUT= WORK.SME

     DATAFILE= "E:\project\file.xls"

      DBMS=EXCEL REPLACE;

     RANGE="SME$";

     GETNAMES=NO;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

proc transpose data=sme out=want (drop=_Smiley Happy;

  var f2-f5;

run;

proc sql noprint;

  select 'col'||put(monotonic(),1.)||'='||F1

    into : renames separated by " "

      from sme

  ;

quit;

data want;

  set want (rename=(&renames.));

run;

I think that will correct your problem.

Art

Occasional Contributor
Posts: 15

Re: Execel importe problem

First, thanks for your (and others) replies.

One piece I don't undstand from your code. I am new to Proc sql. There, I see you are creating:

col1=ProjNum

col2=ClaimNum

col3=PreAdv

col4=PreAdjR

col5=LasClaim

then you store them in the Macro variable 'renames'. (right?)

What does the part of ' separated by " "  ' do? 

-----------------------------------------------------------------

proc sql noprint;

  select 'col'||put(monotonic(),1.)||'='||F1     

    into : renames separated by " "          

from sme

  ;

quit;

data want;

  set want (rename=(&renames.));

  /*not sure what is it doing here. Create in Sasdata set a new variable 'rename' taking values from Macro variable '&renames.'  */

/*why there is a dot following '&renames' */

run;

-----------------------------------------------------------

I got the error message when I try your code.


330  proc sql ;
331  select 'col'||put(monotonic(),1.)||'='||F1
332  into : renames separated by " "
333  from npo1
334  ;
335  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


336  data want;
337   set want (rename=(&renames.));
ERROR: Variable col1 is not on file WORK.WANT.
ERROR: Variable col2 is not on file WORK.WANT.
ERROR: Variable col3 is not on file WORK.WANT.
ERROR: Variable col4 is not on file WORK.WANT.
ERROR: Variable col5 is not on file WORK.WANT.
ERROR: Variable col6 is not on file WORK.WANT.
ERROR: Variable col7 is not on file WORK.WANT.
ERROR: Variable col8 is not on file WORK.WANT.
ERROR: Variable col9 is not on file WORK.WANT.
ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.WANT.
338  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

PROC Star
Posts: 7,363

Re: Execel importe problem

As I indicated in a later post, Tom's suggested transpose code using the ID statement, accomplishes the same thing and much more directly.

Anyhow, to answer your question, the separated by " " simply puts spaces between all of the information. If you don't included the separated by space (i.e., " ") statement you would end up with:

col1=ProjNumcol2=ClaimNumcol3=PreAdvcol4=PreAdjRcol5=LasClaim

which SAS wouldn't know what to do with.

As for the error you got, it looks like you either didn't run the proc transpose as I had suggested, or included something else in the call that changed the variable names.  Otherwise, proc transpose would have created new variables labeled by column numbers.

Again, Tom's suggested code obviates the need to run anything other than the procs import and transpose.

Super User
Posts: 17,829

Re: Execel importe problem

If its a one-off the transpose in excel via Paste Special>Transpose.

If its occurring with multiple datasets then use DDE to read in the file.

Super User
Super User
Posts: 6,500

Re: Execel importe problem

Just make sure to use the GETNAMES=NO setting on PROC IMPORT.

Then use PROC TRANSPOSE in SAS.

proc transpose data=have out=want;

  id f1;

  var f2 - f5 ;

run;

PROC Star
Posts: 7,363

Re: Execel importe problem

Tom's suggestion accomplishes the same thing that my suggested code does, but I definitely recommend using his!

Frequent Contributor
Posts: 104

Re: Execel importe problem

And you can drop the name and label variables that gets created:

PROC IMPORT OUT= WORK.test

            DATAFILE= "XXXXXXXXXXXXX.xls"

            DBMS=EXCEL REPLACE;

     SHEET="XXXXXXX";

     GETNAMES=NO;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

proc transpose data=test out=desired(drop=_name_ _label_);

    id f1;

run;

Ask a Question
Discussion stats
  • 9 replies
  • 192 views
  • 1 like
  • 6 in conversation