SAS transfered wrong value from an Excel sheet.

[Leejean]

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;

data readindata;

     set xlslib."sheet1$"n (DBFORCE=YES DBSASTYPE=(F3='char(255)');

run;

[ballardw]

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.

[Leejean]

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).

[Reeza]

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.

[Linlin]

try:

Libname xlslib excel "&path\myexcelsheet.xls" mixed=yes header=no direct_sql=no scan_textsize=no stringDates=no scantime=no;

[Leejean]

It didn't work.

[Linlin]

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;

data want;

  set xlslib.'sheet1$'n;

  text=put(f1,date5.);

  proc print;run;

                                      Obs           F1    text

                                       1     05APR2012    05APR

                                       2     06MAY2012    06MAY

                                       3     07JUN2012    07JUN

libname xlslib clear;

[Leejean]

Thanks, Linlin!

I don't want SAS interpret a value 5-apr in an Excel cell  as a date because it is not.

[ballardw]

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.

[Leejean]

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;

data _null_;

     file cmds;

     %sysexec 'mypath\monlylog.xls';

run;

data state;

     infile mytab dsd dlm='09'x missover pad lrecl=32767;

     informat F1-F3 $225.;

     format F1-F3 $255.;

     input F1-F3;

run;

data _null_;

     file cmds;

     put "[FILE-CLOSE()]";

     put "[QUIT90]";

run;

Thanks all for your commnents.

[Leejean]

Sorry for the typo. [QUIT90] should be [QUIT()].