The SAS Output Delivery System and reporting techniques

ODS tagsets.excelxp and Data Step

Reply
Regular Contributor
Posts: 151

ODS tagsets.excelxp and Data Step

Hello,
SAS-users.

I'm new to ODS tagsets.excelxp. I've always used DDE to output reports to Excel.
But now I'm trying to use ODS tagsets.excelxp and Data Step to prepare a report.

I can't find out if is it possible to apply Excel attributes (formats and so on) in Data Step using tagsets.excelxp.
I've read some documents.
But examples shows how to use style elements in proc report or proc print with tagattr option.

For example, the code from help documentation shows how to use proc print and tagattr option:

proc print data=sashelp.class
contents="sashelp.class";
id Name;
var Sex;
var Age Height Weight / style={tagattr='format:##0.0'};
sum Age Height Weight;
run;

I've tryed to assign Excel format to numeric data using tagattr option in Data Step in different ways but with no success.

My last attempt is the code below.
The program uses proc template to define style
with Excel format (tagattr = 'Format:#,##0') for data.
Then I create table definition "do1" and assign that style to columns.
There are no mistakes in a log, but when I open output I see that cells are not formatted as I expected (with Format #,##0 ).

How to apply Excel style elements with ODS tagsets.excelxp in Data Step?
Any examples would be great.

I use SAS 9.1.3.

Thanks in advance.


proc template;
define style myjournal;
parent = styles.Journal;
style data from data / tagattr = 'Format:#,##0';
end;
run;

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;

ods listing close;
options missing=' ';

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' );

data _null_;
file print ods=( template='do1'
variables=(acc1 (label="Section 1" format=$8.)
accnr1 (label="Name 1" format=$50.)
acc4 (label="Section 2" format=$8.)
accnr4 (label="Name 2" format=$60.)
m1 (label='January 10') m2 (label='February 10')
m3 (label='March 10') m4 (label='April 10')) );
set test1 end=eof; by code1 type acc1;
retain tot1-tot4 0; array tot tot1-tot4; array mm m1-m4;
if type='P' then do i=1 to dim(tot); tot(i)=tot(i)+mm(i); end;
if first.acc1 then put acc1 accnr1 acc4 accnr4 m1-m4 _ods_;
else put '09'x '09'x acc4 accnr4 m1-m4 _ods_;
if last.type then put /;
if eof then put "Profit" '09'x '09'x '09'x tot1-tot4 _ods_;
run;

ods tagsets.excelxp close;
ods listing;
options missing='.'; Message was edited by: Oleg_1976
SAS Super FREQ
Posts: 8,743

Re: ODS tagsets.excelxp and Data Step

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]
Regular Contributor
Posts: 151

Re: ODS tagsets.excelxp and Data Step

Cynthia,

thank you very much for detailed explanation.

With your help i achieved the desired result in Data Step.
As you point out my mistake was in defining style element.

I prefer Data Step to output custom reports because data step provides very flexible functionality for reporting.

Regards,
Oleg. Message was edited by: Oleg_1976
SAS Super FREQ
Posts: 8,743

Re: ODS tagsets.excelxp and Data Step

Ask a Question
Discussion stats
  • 3 replies
  • 175 views
  • 0 likes
  • 2 in conversation