DDE alternative for SAS under Unix

[keyreal]

Hello all!
I have been using DDE to automate my SAS reports by sending commands to MS Excel regarding formatting, sub-totals, etc. However, our company is switching from PC SAS to Unix SAS which does not support DDE. Does anybody know any close alternative of the "DDE" functionality that would allow me to automate SAS reports exported to Excel? Thanks!

[Cynthia_sas]

Hi:
You can generate subtotals for your data with PROC REPORT and/or PROC TABULATE and/or PROC PRINT. To have Excel respect the formatting (colors and fonts and print controls), have you tried ODS???

ODS HTML or ODS MSOFFICE2K can create ASCII text HTML files that Excel can open and render -- the downside of HTML methods is that while formatting is respected, printing controls and multi-sheet workbooks are harder to achieve.

ODS TAGSETS.EXCELXP can create ASCII text Spreadsheet Markup language XML files that Excel can open and render. Formatting (colors and fonts) is do-able and print control is do-able (using suboptions) and multi-sheet workbooks that respect the formatting canbe easily achieved. The downside of TAGSETS.EXCELXP is that graph object insertion is not allowed (by Microsoft design -- for the XML file).

Depending on what you were doing with your DDE, you might find that TAGSETS.EXCELXP is an alternative.

The program below uses a PROC REPORT to produce subtotals with spanning headers and formatting changes shown with both ODS MSOFFICE2K and ODS TAGSETS.EXCELXP. You can compare the two outputs to see which might work best for you.

cynthia
[pre]
ods tagsets.ExcelXP file='websales_xml.xls' style=sasweb;
ods msoffice2k file='websales_ht.xls' style=sasweb;
proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Canada', 'Pacific');
title 'Region Sales';
column ('Spanning Header1' Region Product) ('Other Header' Sales n);
define Region /group;
define Product / group;
define Sales / mean "Avg Sales";
define n / 'Number of Sales';
break after region / summarize;
rbreak after /summarize;
compute after region;
line ' ';
endcomp;
run;

proc report data=sashelp.shoes nowd;
where region in ('Asia', 'Canada', 'Pacific');
title 'Product Sales';
column ('Spanning Header1' Product Region) ('Other Header' Sales n);
define Product /group;
define Region / group;
define Sales / mean "Avg Sales";
define n / 'Number of Sales';
break after product / summarize;
rbreak after /summarize;
compute after product;
line ' ';
endcomp;
run;
ods _all_ close;
title;
[/pre]