topic Re: help xls file -----> SAS data in SAS Health and Life Sciences

help xls file -----> SAS data

deleted_user — Mon, 30 Apr 2007 18:56:10 GMT

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!

Re: help xls file -----> SAS data

deleted_user — Mon, 30 Apr 2007 19:26:01 GMT

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.

Re: help xls file -----> SAS data

Olivier — Wed, 02 May 2007 10:52:21 GMT

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

Re: help xls file -----> SAS data

LawrenceHW — Fri, 11 May 2007 11:04:33 GMT

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

Re: help xls file -----> SAS data

deleted_user — Thu, 24 May 2007 07:43:39 GMT

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