help xls file -----> SAS data

[deleted_user]

I import an excel file into sas, and when in PROC PRINT, the character variable appeared in OUTPUT like missing, how shall I do? Thank you very much!

[deleted_user]

Oh, I forget to point out that I used the import wizard to import the excel file, and then how could I tell the computer some of them are character variable and some are numeric variables? Thank you very much.

[Olivier]

Hi.

Sometimes the SAS wizard gets wrong about the variable types when it imports Excel files. This is because it only scans the first EIGHT rows of data, and guesses the type from that, whatever cell formats used in Excel.

You can use a PROC IMPORT program instead, with the useful MIXED=YES option to import all mixed-data columns as characted instead of numeric with missing values :
PROC IMPORT DATAFILE = "my Excel workbook.xls"
OUT = mySASdataset
REPLACE
DBMS = EXCEL ;
SHEET = "Excel Sheet name$" ;
GETNAMES = YES ; /* if there are columns names on 1st row */
MIXED = YES ;
RUN ;

Regards.
Olivier

Additional information here : http://support.sas.com/techsup/unotes/SN/004/004924.html

[LawrenceHW]

The MIXED=YES option is a V9 update. If you're still using V8 you might need to change the actual spreadsheet using the Data>Text to Columns from the Excel menus.

Cheers,
Lawrence

[deleted_user]

Be aware also that the import wizard in Enterprise Guide 4.1 does not use PCFF by default to read Excel spreadsheets. The result is that cells with very long text may be truncated or split over two rows. The issue also arises where a carriage return is embedded within the etxt in a cell.

There is a support note on this subject.

Kind regards

David