BookmarkSubscribeRSS Feed
Inp
Obsidian | Level 7 Inp
Obsidian | Level 7
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;
2 REPLIES 2
Ross
Calcite | Level 5
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
Inp
Obsidian | Level 7 Inp
Obsidian | Level 7
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")]';

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1069 views
  • 0 likes
  • 2 in conversation