topic Re: Proc Report Summarize in ODS and Base Reporting

Proc Report Summarize

bilardi — Mon, 18 Apr 2011 16:44:07 GMT

I have the following piece of code in my proc report

rbreak after /summarize;

How can I format each of the summarized columns differently...some are dollars, some are counts.

Any help is greatly appreciated.

Re: Proc Report Summarize

Cynthia_sas — Mon, 18 Apr 2011 17:27:14 GMT

Hi:
Generally, if you use a format in your DEFINE statement, you can have different formats used for different columns.

cynthia
[pre]
ods html file='c:\temp\summarize.html' style=sasweb;

proc report data=sashelp.class(obs=5) nowd;
column name sex age height weight;
define name / order;
define sex / display;
define age / sum f=comma10.;
define height / sum f=dollar8.2;
define weight / sum f=dollar8.2;
compute age;
age.sum = age.sum * 10000;
endcomp;
rbreak after / summarize style=Header;
run;

ods _all_ close;
[/pre]

Re: Proc Report Summarize

bilardi — Mon, 18 Apr 2011 20:20:28 GMT

I'm using tagsets.excelxp as my destination. It does not seem to work. Would if be different syntax?

Re: Proc Report Summarize

Cynthia_sas — Mon, 18 Apr 2011 20:57:33 GMT

Hi:
Using the HEADER style for the summary line would be the same syntax using TAGSETS.EXCELXP. If you want Excel to use a different numeric format (other than the default General format) when it opens the XML file, you must use the TAGATTR style attribute to send a MICROSOFT format to Excel from SAS. With my version of TAGSETS.EXCELXP, the dollar formats are respected, but the COMMA format is not respected -- so you would have to use the TAGATTR style attribute to send a Microsoft format:
[pre]
define age / sum f=comma10.
style(column)={tagattr="##,###,###"};
[/pre]

For more information about Microsoft formats and TAGSETS.EXCELXP, see my SAS Global Forum paper: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

cynthia

Re: Proc Report Summarize

bilardi — Tue, 19 Apr 2011 12:51:19 GMT

Thank you so much. I got the desired output. I just have one last question.

I had defined a style in a PROC TEMPLATE which included a "tagattr" format. But it wasn't being carried over to the summary. But it worked fine when I added the style override in the Define statement. Can you explain why this might happened.

Thanks so much for your time and help.

--Brian

Re: Proc Report Summarize

Cynthia_sas — Tue, 19 Apr 2011 16:53:48 GMT

Hi:
I don't have a definitive answer about why using TAGATTR didn't work in a style template. I suspect that it's because TAGATTR is generally used with numeric variables only (you are sending a Microsoft number format using TAGATTR) -- and the TAGATTR value you specified might not have been appropriate for ALL the variables in your report. (TAGATTR would have been wrong for the NAME variable, for example.)

Everytime I use TAGATTR, I use it in a DEFINE statement (REPORT) or a VAR statement (PRINT) or, with TABULATE, in a TABLE statement using STYLE= overrides. That way, I have the most control over which format is explicitly used for which variable.

If you want to pursue why your style template did not work, you'll have to open a track with Tech Support. When I use the style template below, I do get TAGATTR used, even with character variables in the report.

cynthia
[pre]
data class;
set sashelp.class;
idvar = _n_ * 10000;
age = age * 10000;
run;

ods path work.tmp(update) sasuser.templat(update)
sashelp.tmplmst(read);

proc template;
define style styles.ta;
parent = styles.sasweb;
class data from data/
background=yellow
tagattr="##,###,###";
end;
run;

ods tagsets.excelxp file='c:\temp\sum_temp.xls' style=styles.ta;

proc report data=class(obs=5) nowd;
column idvar age height weight;
define idvar / order;
define age / sum;
define height / sum;
define weight / sum;
rbreak after / summarize style=Header;
run;

ods _all_ close;

ods tagsets.excelxp file='c:\temp\sum_temp_withchar.xls' style=styles.ta;

proc report data=class(obs=5) nowd;
column idvar name sex age height weight;
define idvar / order;
define name / display;
define sex / display;
define age / sum;
define height / sum;
define weight / sum;
rbreak after / summarize style=Header;
run;

ods _all_ close;
[/pre]