help xls file -----> SAS data

Reply
N/A
Posts: 0

help xls file -----> SAS data

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!
N/A
Posts: 0

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

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.
Super Contributor
Posts: 260

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

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
Contributor
Posts: 49

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

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
N/A
Posts: 0

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

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
Post a Question
Discussion Stats
  • 4 replies
  • 379 views
  • 0 likes
  • 3 in conversation