Excel output using proc report

[deleted_user]

I need to create an excel template in such a way that the first sheet of the excel workbook will contain a default title and a link to the next worksheet. The next worksheet will contain the actual report which may change from user to user. I need to write a generic macro for this without using sas dde. The output shud be like this:

Sheet1:
C1R1 C2R2
Default Title (in bold and font size = 24) Sub Title (font size = 10)




C1R15
Link to next worksheet


I have two approaches:
1. Create a dataset and store the titles in respective column and rows and then generate the output.
2. use xml. However i dont have xml mapper.

Which approach shud chosen?

[Cynthia_sas]

Hi:
You have several choices for creating files that Excel can open using the Output Delivery System:
1) create an ASCII text comma-separated file using ODS CSV or ODS CSVALL
2) create an ASCII text HTML file using ODS HTML, ODS HTML3 or ODS MSOFFICE2K
3) create an ASCII text Spreadsheet Markup Languate XML file using ODS TAGSETS.EXCELXP

Since you want a multi-sheet workbook, that would point to creating an Excel 2003 XML Spreadsheet Markup Language file. You do _NOT_ need the XML Mapper to create this type of XML. ODS knows how to write the appropriate XML tags that conform to the Microsoft specification when you use this destination.

However, I do not understand what you mean by saying that you want to create an Excel TEMPLATE -- if by Excel TEMPLATE you mean that you want to create a proprietary .XLT file -- I believe the only way to create an .XLT file is to use Excel.

Second, you say you want to have items placed in specific rows or specific columns. Generally, when you create output, it starts in row 1, column 1 and columns are filled from left to right, based on the procedure that you used to create your output. So it would be hard for you to have something in row 1, skip some rows and then have something else in row 15....if you needed for something to absolutely be on row 15, you'd have to rig up some data to have something in the first obs and then some obs of missing values and then something in the 15th obs.

But, before you go down the road of creating your own title and links via a dataset, I'd recommend that you investigate what TAGSETS.EXCELXP will do for you using the CONTENTS= suboption -- which automatically creates a worksheet of links to other sheets.

If you are running SAS 9.1.3, I'm not sure when the CONTENTS= suboption was implemented for TAGSETS.EXCELXP. If it doesn't work for you, then you should investigate getting the newest ExcelXP tagset so you can use CONTENTS= -- it is much nicer than building your own TOC.

cynthia
[pre]
ods tagsets.excelxp file='somefile.xml'
style=sasweb
options(contents='yes');

ods tagsets.excelxp options(sheet_name='Report');
ods proclabel 'First Report';
proc report data=sashelp.shoes nowd
contents='Something Else';
column region product sales;
define region / group;
define product / group;
define sales / sum;
break after region / summarize;
compute after region;
line ' ';
endcomp;
run;

ods tagsets.excelxp options(sheet_name='Freq');
ods proclabel 'Second Report';
proc freq data=sashelp.shoes ;
tables subsidiary /contents='Freq';
run;

ods tagsets.excelxp options(sheet_name='Means');
ods proclabel 'Third Report';
proc means data=sashelp.shoes;
var returns;
class product;
run;
ods tagsets.excelxp close;

[/pre]