Hi:
The way you are specifying the style= option is incorrect. The defined STYLE name, MYJOURNAL -- goes into the ODS invocation:
[pre]
ods tagsets.excelxp file="d:\work\test_xml_1.xls" style=myjournal
options(sheet_name="test" embedded_titles='Yes'
Embed_Titles_Once='Yes' Frozen_Headers='Yes' );
[/pre]
So this part of your code is correct. However, in the TABLE template, you ALSO use the name of the style template, which is incorrect:
[pre]
proc template;
define table do1;
column acc1 accnr1 acc4 accnr4 m1-m4;
define m1;style= myjournal;end; define m2;style= myjournal;end;
define m3;style= myjournal;end; define m4;style= myjournal;end;
end;
run;
[/pre]
You need to point to the style ELEMENT name in your DEFINE block...not the style TEMPLATE name. I assume that since your style template is changing the DATA style element, that you want that particular Microsoft format to be used for all the m1-m4 columns. In this case, the correct syntax would be:
[pre]
proc template;
define table do1;
column acc1 accnr1 acc4 accnr4 m1-m4;
define m1;
style= data;
end;
define m2;
style= data;
end;
define m3;
style= data;
end;
define m4;
style= data;
end;
end;
run;
[/pre]
In this way, the table template column becomes linked to the style template definition for a single style ELEMENT.
I'm not sure what your DATA step program is doing, however. Not sure why you're writing a TAB character into a cell. TAGSETS.EXCELXP is XML output -- Spreadsheet Markup Language -- not CSV or TAB-delimited output. Also, typically, LISTING techniques such as PUT / or PUT @34, etc do NOT work with ODS and DATA _NULL_.
It almost seems to me that you are calculating and then writing your own totals or subtotals???? Why not use PROC REPORT to create a summary report...something like what's shown??? That will simplify your life. If you really need the test for TYPE='P' (which only seems to be accumulating the total), then you can use "hidden" report items in the PROC REPORT step. But, I'm not convinced you need to go down the DATA step road at this point. The REPORT code below shows the use of a custom style template to set the TAGATTR attribute and also shows how to customize the various break lines (for country and region and the grand total at the bottom of the report). You may not need all of these customized breaks, but it does show off the kinds of things you can do with PROC REPORT that is harder to do with PROC PRINT.
And, this code below creates a "detail" report -- where every report row represents an observation in the data file or subset. If you changed the usage of these 4 variables to GROUP usage, you would get a 'summarized' report -- where one report row represented a group of observations. Very cool of PROC REPORT to work that way!
[pre]
define country /group;
define region / group;
define division / group;
define product / group;
[/pre]
cynthia
[pre]
proc template;
define style myjournal;
parent = styles.Journal;
style data from data / tagattr = 'Format:#,###,##0';
end;
run;
** make some data;
proc sort data=sashelp.prdsale out=prdsale;
by country region division;
where country in ('GERMANY', 'CANADA') and
product in ('TABLE', 'CHAIR');
run;
data test1;
length country $25 region $25 division $3 ;
set prdsale;
label country = "Section 1"
region = "Name 1"
division = "Section 2"
product = "Name 2"
m1='January 10'
m2='February 10'
m3='March 10'
m4='April 10' ;
m1 = int(actual * predict / 100);
m2 = int((actual /7) * 10);
m3 = abs(int((actual/10) - (predict/10) * 17));
m4 = int(predict + actual / 1713);
format country region product division;
run;
ods tagsets.excelxp file="c:\temp\report_xml_1.xls" style=myjournal;
proc report data=test1 nowd;
column country region division product m1 m2 m3 m4;
define country /order;
define region / order;
define division / display;
define product / display;
define m1 / sum;
define m2 / sum;
define m3 / sum;
define m4 / sum;
break after country / summarize;
break after region / summarize;
rbreak after / summarize;
compute after country;
country = 'Country SubTotal';
endcomp;
compute after region;
region = 'Region SubTotal';
endcomp;
compute after;
country = 'Grand Total';
endcomp;
run;
ods _all_ close;
ods listing;
options missing='.';
[/pre]