topic Re: excel doesn' t recognize date format in ODS and Base Reporting

excel doesn' t recognize date format

Inp — Wed, 31 Mar 2010 22:08:26 GMT

Hi Dear,
Here is my coding. When I run the below coding I am able create MYTEST.xls but when I open the xls sheet, and try to use the custom filter, then I try to use ' is greatter than' or 'is less than' from the drop down panel , it doesn't work. Basically it seems, it doesn't recognize the date format. Can any one help to me fix this problem. I am using SAS 9.2 on Windows XP service pack 3. Do I have install any SAS patch to fix this issue?

DATA TEMP;
X='23MAR2009'D;
OUTPUT;
X='22APR2007'D;
OUTPUT;
X='22MAR2010'D;
OUTPUT;
RUN;
ods listing close;

ods tagsets.excelXP path='C:\' file='MYTEST.xls' style=newstyle rs= none;
ods tagsets.excelXP options(
Sheet_Name='TEST' Embedded_Titles='YES'
Embedded_Footnotes = 'YES' Embed_Titles_Once='YES'
Suppress_Bylines='YES' Sheet_Interval='NONE'
Frozen_Headers='2' Autofilter='ALL'
Absolute_Column_Width='10'
);
run;

PROC PRINT DATA=TEMP;
FORMAT X DATE9.;
RUN;
ods _all_ close;
ods listing;

options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /e';
data _null_;
x=sleep(3);
run;
filename cmds dde 'excel|system' ;
data _null_;
file cmds;
put '[app.minimize]';
put '[open("C:\MYTEST.xls")]';
put '[error("false")]';
put '[SAVE.AS("C:\MYTEST.xls",1)]';
put '[close("false")]';
put '[quit()]';

run;

Re: excel doesn' t recognize date format

Ross — Wed, 31 Mar 2010 22:30:41 GMT

You need to add a style definition to your Proc Template code that created your style called "newstyle". Add the following:

style data_date9 from data /
just=right
tagattr='format:ddmmmyyyy type:DateTime';

Then your Proc Print should read:

PROC PRINT DATA=TEMP;
var X / style(data)=data_date9; /*Add this row*/
FORMAT X DATE9.;
RUN;

Also, in the ODS tagsets.excelXP statement, the file statement should have a .XML suffix not .XLS. You would also need to change it in your DDE section under the file open command. fixed .xlm to .xml

Message was edited by: Ross

Re: excel doesn' t recognize date format

Inp — Wed, 31 Mar 2010 22:56:53 GMT

I made the below changes. I put only the coding that I made changes on the existing program that I posted before. but I am getting error below error :

ERROR: DDE session not ready.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase.

the coding that I made changes are below
----------------------------------------------------------------

proc template
define style newstyle;
parent = styles.normal;

style data_date9 from data /
just=right
tagattr='format:ddmmmyyyy type:DateTime';
end;
run;

ods tagsets.excelXP path='C:\' file='MYTEST.xml' style=newstyle rs= none;

PROC PRINT DATA=TEMP;
var X / style(data)=data_date9; /*Add this row*/
FORMAT X DATE9.;
RUN;

put '[open("C:\MYTEST.xml")]';