BookmarkSubscribeRSS Feed
bilardi
Calcite | Level 5
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.
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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]
bilardi
Calcite | Level 5
I'm using tagsets.excelxp as my destination. It does not seem to work. Would if be different syntax?
Cynthia_sas
SAS Super FREQ
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
bilardi
Calcite | Level 5
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
Cynthia_sas
SAS Super FREQ
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 938 views
  • 0 likes
  • 2 in conversation