Excel to SAS 1

[msand]

I am trying to read Excel dataset into SAS using Import Wizard and somehow one of the date fields is not comming, in SAS it's empty. If the first record has empty field in the date, SAS doesn't read all column, if the first record is not empty, SAS reads the column perfectly. Also the format of Excel cell is the same for all dates, but some are comming into SAS anb some are not. Any suggestions?
What is the best way to read Excel without Import Wizard?

Thanks

 

[klaz2002]

Have you tried proc import? The wizard will generate code that will set the column types based on the first value of data. The Proc import is supposed to check the first 10 values (rows) of data.
If the proc import doesn't help why not use the wizard to start the process. Have the wizard save the import code as your_sas_file.sas. Then go and tweak the code.

What sas does is set up the INPUT statement based on what it feels the datatype should be.
ex.
input var1 $ var2 $ .....;
All you have to do is tell SAS that the char var (for that date column) should be numeric (so take out the '$' from that column in the input statement.) You may also need to change the informat for that var/column as it is prob set to a char informat.

I hope that this has helped you.
Klaz

 

[6656]

You can format that column to 'text' instead of 'gerneral' on your execel sheet and re-save the file. Re-try to use proc import again.

In your case, if the first record is empty and format is 'general or numeric', sas will treat this column to numeric. Therefore, any charact value afterward will be invalid value and treat them as missing.

Mike

 

[Musicman199]

You don't indicate what platform you are running so my way may not work for you, but I work on Mainframe, and here is how I do this.

I open the EXCEL sheet and do File/Save As and save it as undelimited .prn file. The outcome is a simple flat text file that easily and dependibly FTPs up to my mainframe. I then read the file into my SAS program and go from there - works every time. Just be sure to set the column widths a couple of spaces wider than the data or it will truncate. (This works for ACCESS, too).

 

[ChrisW75]

One thing I've done a few times when reading in Excel spreadsheets is set everything to text, then sort it out once I've read it in using the input() function
ie sasdate = input(text_date,ddmmyy10.);