topic Re: wired problem of import an excel file in SAS Procedures

wired problem of import an excel file

Mike_Davis — Fri, 21 Sep 2012 19:30:54 GMT

Hello everyone,

I have a excel file with one column,

I want to import it into SAS and get an character variable,I attached the excel file.

My problem is : if I opended the excel file then run the SAS code, the variable will be like 0.745 for the first obs ,but if I close the excel file then run the SAS code ,the value of the first obs will be changed to 74.5%.

Could anyone help me point out the reason of the problem?

Thanks!

Mike

PROC IMPORT

OUT= _test2

datafile="c:\temp\oops.xls"

DBMS=EXCEL REPLACE;

GETNAMES=YES;

MIXED=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Re: wired problem of import an excel file

Doc_Duke — Mon, 24 Sep 2012 14:29:47 GMT

delete the first two blank rows so PROC IMPORT will work as intended.

The data in the spreadsheet are actually numeric with a format applied by Excel.

Re: wired problem of import an excel file

JRTait — Wed, 01 May 2013 18:39:04 GMT

Mike,

If you do want this variable to be a character variable you can use the following option within your PROC IMPORT:

DBDSOPTS= "DBSASTYPE=(%_of_value='Char(4)')";

When running this you may also want to change the MIXED option from YES to NO.

Re: wired problem of import an excel file

Ksharp — Thu, 02 May 2013 02:14:57 GMT

Interesting , If you use another engine XLS ,you will different result:

PROC IMPORT

OUT= _test2

datafile="c:\temp\oops.xls"

DBMS=xls REPLACE;

GETNAMES=YES;

MIXED=YES;

RUN;

Ksharp