05-04-2012 04:50 PM
I was pulling infomation from an excel sheet using libname and data step. See the code below. SAS read cell with "5-apr" as 41004 and "5-Jan" as 5 which make non sense at all. I try to let SAS read this column as character, but still get the same result. Is there any way to let SAS know this is not a date or time?
Libname xlslib excel "&path\myexcelsheet.xls" mixed=yes header=no direct_sql=no scan_textsize=no stringDates=yes scantime=yes;
set xlslib."sheet1$"n (DBFORCE=YES DBSASTYPE=(F3='char(255)');
05-04-2012 05:45 PM
Excel is telling SAS it is a date or rather the internal value that Excel stores for a given date. Fix the display format in Excel to show numeric and see what the values are. If your Excel works like mine if you type 4-5 (4 dash 5) in a cell without format Excel will ASSUME it is a date and show 5-Apr. Change the display to a numeric and see the VALUE Excel has saved: 41004.
The date offsets Excel and SAS use are different. By the way a SAS value of 5 when displayed as a date is 01/06/1960 and 41004 comes out at 04/06/2072.
If you are entering data in Excel it is a good idea to set the column display as string or numeric BEFORE entering data.
05-07-2012 10:17 AM
You're right. When I typed 4-5 in the cell, it showed 5-apr. My problem is that the Excel is a template we send out to collect information from 53 states monthly. This specific column store various information includes both numeric and character values. People who fill in this form are not consistent every time even we emphasis the instruction every time. I’m looking for a SAS option or a way to ask SAS only read this column as it is (like character string).
05-07-2012 12:37 PM
Change your template using the data validation tools built into Excel.
Create a second, hidden worksheet, with all possible dates for the range required and have it validated against that field and then you'll be fine in the future.
05-07-2012 10:38 AM
It worked when I tested at home. It doesn't work in my office . I have PC sas 9.3. I created an excel file with 3 records(5-apr,6-may,7-jun) and run the code below.
Libname xlslib "c:\temp\forum\test.xls" mixed=yes header=no direct_sql=no scan_textsize=no stringDates=no scantime=no;
Obs F1 text
1 05APR2012 05APR
2 06MAY2012 06MAY
3 07JUN2012 07JUN
libname xlslib clear;
05-07-2012 10:59 AM
Since you say you are sending a template out for people to enter data the template, before it is sent out needs to have a specific format for each column or cell people enter data into. If the 4-5 type entry is a code value and doesn't mean 4 minus 5 then set the column to text. Any field that has leading zeroes that are significant may need to be set as text as well, for example ZIP codes.
This is a very long standing problem with Excel changing data types from intended string to dates.
05-07-2012 11:42 AM
Using DDE(Dynamic Data Exchange) import Excel file to SAS seems work better and solved my problem.
filename cmds DDE 'excel|system';
filename mytab DDE 'Execl|mypath\[monthlylog.xls]sheetname!R1C1:R10C3' notab;
infile mytab dsd dlm='09'x missover pad lrecl=32767;
informat F1-F3 $225.;
format F1-F3 $255.;
Thanks all for your commnents.