Facing Problems with format while trying to create Excel using ODBC

[Saugata]

Hi,

I am trying to create an EXCEL from sas dataset using ODBC. Everything works fine but when there are some formats (both system and user defined) present in the data it seems Excel is not able to interpret it correctly. Sometimes the values are going into excel instead of format. The below code will elaborate on my problem:

Proc Format;
Value $Daypart '1' ='Morning'
       '2' ='Daytime Weekdays'
       '3' ='Daytime Weekend'
       '4' ='Early Prime'
       '5' ='Peak Prime'
       '6' ='Late Prime'
       '7' ='Night';
Run;

Data Curve_info;
format Daypart $Daypart. date date9. time time8. ;

  input Daypart date  time;
   datalines;
1 19368 21600
4 19368 72000
5 19368 75600
6 19368 82800
;
run;

libname rfxls odbc noprompt="DBQ=C:\Excel_details.xls;

driver={microsoft excel driver (*.xls)}; readonly=0"
;

data rfxls.Sheet1;
set curve_info;
run;
libname rfxls clear;

I am getting the Excel_details.xls file as:

Daypart	date	time
1	1/10/2013	1/1/1960
4	1/10/2013	1/1/1960
5	1/10/2013	1/1/1960
6	1/10/2013	1/1/1960

One can see from here that the Daypart format is not there (only the numeric values), Date field is appearing in ddmmyy10. format instead of date9. and Time field is appearing completely wrong.

It seems that excel is only getting the values from SAS dataset and then applying it's own format according to its understanding.

Is there a way to keep the formats as it appears in sas into excel file using ODBC?

Thanks in advance for your help.

Regards,

Saugata Mukherjee.