Help using Base SAS procedures

Proc Import(Excel file) with fixed column formats

Reply
Occasional Contributor
Posts: 12

Proc Import(Excel file) with fixed column formats

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.

Super User
Posts: 17,819

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

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.

Frequent Contributor
Posts: 138

Proc Import(Excel file) with fixed column formats

No idea on this.May be i will check and see if i can help .Smiley Happy

Ask a Question
Discussion stats
  • 2 replies
  • 731 views
  • 3 likes
  • 3 in conversation