Help using Base SAS procedures

'ALL ' variable label in PROC TABULATE based on Subgroups

Reply
Frequent Contributor
Posts: 86

'ALL ' variable label in PROC TABULATE based on Subgroups

Hi ,

Is there a way in PROC TABULATE to label the ALL variable used for subgroup totals according to the name of the Subgroup? For e.g. if there are two Subgroups in the PROC TABULATE namely A and B , then is it possible to label the ALL variable for these groups as "Subtotals for A" and "Subtotals for B" respectively instead labeling them with the same name like "Subtotals" as is done usually.
PROC Star
Posts: 1,561

Re: 'ALL ' variable label in PROC TABULATE based on Subgroups

You might have to use proc report for that.
Frequent Contributor
Posts: 86

Re: 'ALL ' variable label in PROC TABULATE based on Subgroups

Thanks Chris.Will use PROC REPORT . I hope SAS provides this option to the ALL variable of PROC TABULATE in future versions.
Valued Guide
Posts: 2,175

Re: 'ALL ' variable label in PROC TABULATE based on Subgroups

If I understand correctly, you wish to label an ALL total (or sub-total) cell.
That feature has been available in the TABULATE Procedure for .... (alongtime=>10yrs)

table ( &type1 all="&type1 totals"), ( all= 'row total' &type2=' ' )

as demonstrated below, key words like ALL and statistics like SUM and N can be labelled "in context" inside the TABLE statement. It should be in the manual [pre]proc tabulate data= sashelp.class ;
%let type1 = age ;
%let type2 = sex ;
class &type1 &type2 ;
table ( &type1 all="&type1 totals")
, ( all= 'row total' &type2=' ' )
* n=' ' *f= comma5.0
/ box= 'cross counting sashelp.class'
rts= 20 ;
run ; [/pre]
Frequent Contributor
Posts: 86

Re: 'ALL ' variable label in PROC TABULATE based on Subgroups

Thanks for your reply Peter, I actually meant to label the ALL variable within the same dimension of the table based upon the group.For eg if the Age Variable had two subgroups eg 'Young' and 'Old' then i wanted the subtotals for these subgroups to be labeled as "Subtotals for the Young" and Subtotals for the Old" .Anyway i guess i will use PROC REPORT for now. Message was edited by: pchegoor
Valued Guide
Posts: 2,175

Re: 'ALL ' variable label in PROC TABULATE based on Subgroups

Tabulate labelling information for classLevel and classValue don't join up anywhere.
Pre-processing could do some of it.
Replacing the "all" line/cells with a "total" observation containing the required statistics and for the classValue, it would hold the parent classValue and/or a concatenation of all its parent classValues in the table hierarchy.
Proc means does summarise at the intermediate levels.
Very similarly to the Proc Tabulate TABLE statement, you can layout the cross-classing you want with the TYPES statement of Proc Means. http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a000222729.htm. Apart from the NWAY level each value of _TYPE_ is a subtotal.
More convenient than the traditional _TYPE_, with the proc means CHARTYPE option, the _TYPE_ variable becomes character in a binary style that holds 1 for each relevant class variable and 0 if that output row is a total of that class variable.
Without knowing too much about the class variables, you can use the "1" s in _TYPE_ to signify which class values represent the 'parent' of the current summary line. For each output row from proc means which has a '0' in _TYPE_ you need to replace the blank value of the corresponding class variable, with the string you want to take the place of ALL. In the general approach I'm proposing, the value to replace ALL, will be a character string that is a concatenation of the relevant class variables (those which have a '1' in _TYPE_).
Of course, once this is used as the input to proc tabulate, you won't be able to use the N statistic (an N-statistic or _FREQ_ from proc means should provide the count).
Nor would you use the ALL keyword, because that row is already in the output from proc means.
As proc means output is generated in _TYPE_ order, you would need to create some way of re-ordering the data - most important is whether you want the ALL statistics to come after or before the relevant class group.
Another inconvenience to overcome: numeric class values would need to be converted to character types to allow the replacement of ALL with the parent class value concatenation.

Guess by this stage I'm convinced that PROC REPORT has won over TABULATE in this "battle of the reporting giants" better presented in the paper "SUGI 27: Battle of the Titans: REPORT vs. TABULATE" at http://www2.sas.com/proceedings/sugi27/p133-27.pdf .

PeterC
Ask a Question
Discussion stats
  • 5 replies
  • 206 views
  • 0 likes
  • 3 in conversation