The SAS Output Delivery System and reporting techniques

Moving the totals column in the PROC FREQ

Reply
Occasional Contributor npa
Occasional Contributor
Posts: 16

Moving the totals column in the PROC FREQ

Okay. I know this is a long shot, but does any one know how to manipulate PROC FREQ so that the row totals appear in the first column instead of the last column. Same for the column totals (have them print out at the top, instead of the bottom).
Super Contributor
Posts: 394

Re: Moving the totals column in the PROC FREQ

Sounds like you're talking about the crosstabs table. No, there isn't. You could use the OUT= option to route the output to a data set and print it yourself, though.
SAS Super FREQ
Posts: 8,744

Re: Moving the totals column in the PROC FREQ

The other thing you might try to do is play with PROC TABULATE and see whether that gives you the kind of order to the statistics that you want. Something like what's shown below.
cynthia
[pre]
proc tabulate data=sashelp.prdsale;
where product in ("BED","CHAIR");
class region product;
table (region all)*(colpctn n pctn rowpctn ),
(all product);
run;
[/pre]
Frequent Contributor
Posts: 95

Re: Moving the totals column in the PROC FREQ

I'm wondering if this can be done with a custom table style template. I don't really mess with the style templates much, so I don't really know the answer, but it seems like a good solution if you want to use a more advanced technique. Does anyone know?
SAS Super FREQ
Posts: 8,744

Re: Moving the totals column in the PROC FREQ

Hi!
When you say "custom table style template" it is a bit confusing.

ODS has TABLE templates (those used by procedures to control table structure, columns, formats, etc) and STYLE templates (those templates used by the destinations to set colors, fonts, cellspacing, cellpadding and borders etc). Those are actually 2 separate and different kinds of templates with 2 different syntaxes for defining them. Each procedure "knows" what TABLE template it is supposed to use. Every destination has a default STYLE template that is used unless you override the STYLE with an explicit STYLE= option in the ODS destination statement.

So, moving the totals column CANNOT be done with a custom STYLE template. Moving the totals column MIGHT be accomplished with PROC FREQ, if the output object of interest HAS a table template that can be altered or modified. Building a custom TABLE template and invoking it with DATA _NULL_ is also a possibility, but I would only pursue that after other, simpler techniques had been explored first. (Like the PROC TABULATE approach).

There are 4 procedures which do NOT have TABLE templates: PROC PRINT, PROC REPORT, PROC TABULATE and PROC FREQ "crosstabs" var1*var2 (more about this last item below).

To see what table template is used with a procedure do this:
[pre]
ods listing;
ods trace on / label;
proc freq data=sashelp.shoes;
tables region;
run;

proc means data=sashelp.shoes;
var Sales;
run;

proc freq data=sashelp.shoes;
tables subsidiary * region;
where region in ('Asia', 'Canada');
run;

proc print data=sashelp.shoes(obs=10);
run;

** put any other procedures of interest here to investigate output object names and;
** other output object information, such as templates;

ods trace off;
[/pre]
Then, look in the SAS log for the section that starts with "Output Added:" -- that is the beginning of the ODS TRACE information. Each procedure will produce an information section for each output object created by the procedure. If that output object has a TABLE template, you will find the TABLE template name on the line with "Template:" -- the TRACE information should be echoed directly underneath the procedure statements in the LOG. The presence of a TABLE template name means you might be able to alter the placement of the TOTALS column by modifying the default TABLE template.

If you do not see a "Template:" line in your procedure's TRACE information, then it means that this is one of the procedures that does not have a TABLE template. Your choices for dealing with this fact (and getting the structure you want) are:
1) create an output data set from your procedure of choice and use PROC PRINT or PROC REPORT to create the output. If you use a DATA step program you might choose to also use a custom TABLE template with the DATA step program;
2) use some other procedure, like PROC TABULATE, to create the output directly from the input data;
3) (almost same as 1) create an output data set from your procedure of choice and use a DATA step program to create the output. If you use a DATA step program you might choose to also use a custom TABLE template with the DATA step program.

Starting in SAS 9.2, the simple var1*var2 crosstabs from PROC FREQ will have a TABLE template, per this Tech Support note:
http://support.sas.com/rnd/base/topics/new92/92procs.html
and, when that happens, you could change the TABLE template for a PROC FREQ crosstabs. But until 9.2 is released, you would have to stick with the 3 choices above.

I should mention that there is a PROC FREQ alternative to the simple crosstabs that gives a slightly different look to the same crosstab information and you might like the way this output looks. Try this:
[pre]
proc freq data=sashelp.shoes;
where region in ('Asia', 'Canada');
tables subsidiary * region /crosslist ;
run;
[/pre]

PROC FREQ with the CROSSLIST option does use a TABLE template and that TABLE template CAN be modified and customized.

Long answer to a short question! And I didn't even get into TAGSET templates!
cynthia
Super Contributor
Posts: 394

Re: Moving the totals column in the PROC FREQ

Good description, Cynthia. In the interest of full disclosure I should make it clear that, although the crosstabs table template in 9.2 supports a lot of customizations, it will not let you rearrange the order of the row totals column or the column totals row.
Ask a Question
Discussion stats
  • 5 replies
  • 210 views
  • 0 likes
  • 4 in conversation