topic Re: Execel importe problem in SAS Procedures

Execel importe problem

telescopic — Wed, 20 Jul 2011 20:08:58 GMT

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.

ProjNum	Claim	Adv	Rare	LastClaim
1	4	1	0	1
2	19	1	0.72	0
3	12	0	0.3721	1
4	9	0	0.054	0

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?

ProjNum	1	2	3	4
ClaimNum	4	19	12	9
PreAdv	1	1	0	0
PreAdjR	0	0.72	0.3721	0.054
LasClaim	1	0	1	0

Thank you

Execel importe problem

art297 — Wed, 20 Jul 2011 20:12:44 GMT

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?

Re: Execel importe problem

raveena — Wed, 20 Jul 2011 20:31:08 GMT

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

Re: Execel importe problem

art297 — Wed, 20 Jul 2011 21:51:19 GMT

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

Re: Execel importe problem

Reeza — Wed, 20 Jul 2011 22:16:57 GMT

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.

Re: Execel importe problem

Tom — Thu, 21 Jul 2011 14:58:54 GMT

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;

Re: Execel importe problem

art297 — Thu, 21 Jul 2011 15:03:44 GMT

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

Re: Execel importe problem

telescopic — Thu, 21 Jul 2011 16:23:26 GMT

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

Re: Execel importe problem

art297 — Thu, 21 Jul 2011 16:37:33 GMT

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.

Re: Execel importe problem

DLing — Thu, 21 Jul 2011 16:56:50 GMT

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;