BookmarkSubscribeRSS Feed
telescopic
Calcite | Level 5

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

9 REPLIES 9
art297
Opal | Level 21

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?

raveena
Obsidian | Level 7

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

art297
Opal | Level 21

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=_:);

  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

telescopic
Calcite | Level 5

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

art297
Opal | Level 21

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.

Reeza
Super User

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.

Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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

DLing
Obsidian | Level 7

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1186 views
  • 1 like
  • 6 in conversation