BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!
4 REPLIES 4
deleted_user
Not applicable
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
Pyrite | Level 9
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
Quartz | Level 8
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
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 4 replies
  • 1045 views
  • 0 likes
  • 3 in conversation