topic Proc Import(Excel file) with fixed column formats in SAS Procedures

Proc Import(Excel file) with fixed column formats

rockerd — Mon, 27 Jun 2011 19:30:10 GMT

Hi,

I am importing data from an excel file. I see that whenever SAS sees that a column was left blank even if it was numeric, it treats it like a character variable. This causes problems when I am inserting the excel file records into my master SAS file which has some numeric columns that are imported as character in excel imported data.

Is there any way I can force lengths and formats for the Columns in excel file to be imported?

currently used script:

proc import

datafile="C:\Data\myDATA.xls"

dbms=XLS

out=test ;

guessingrows=10000;

run;

Thanks.

Re: Proc Import(Excel file) with fixed column formats

Reeza — Mon, 27 Jun 2011 21:08:00 GMT

Not as far as I know...

The guessingrows option is also invalid in proc import with Excel

A couple of things you might look into:

1) converting excel file to CSV and doing a proc import. Modify the SAS generated code to make sure your formats are the way you want them to be.

2) Read file with DDE

3) Can try the Force option in proc append, but not sure how that would work. If all the variable that were mismatched are missing in the new set it could be fine, but if variables that are not missing are in diff format you would lose information.

4) If all files are the same, try the option mixed=yes on all files to see if the imports are consistent.

Proc Import(Excel file) with fixed column formats

manojinpec — Tue, 28 Jun 2011 13:06:44 GMT

No idea on this.May be i will check and see if i can help .:)