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

'ALL ' variable label in PROC TABULATE based on Subgroups

pchegoor — Thu, 30 Jul 2009 19:00:58 GMT

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.

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

ChrisNZ — Thu, 30 Jul 2009 23:11:12 GMT

You might have to use proc report for that.

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

pchegoor — Thu, 30 Jul 2009 23:26:25 GMT

Thanks Chris.Will use PROC REPORT . I hope SAS provides this option to the ALL variable of PROC TABULATE in future versions.

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

Peter_C — Fri, 31 Jul 2009 14:03:30 GMT

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]

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

pchegoor — Fri, 31 Jul 2009 18:52:58 GMT

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

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

Peter_C — Sat, 01 Aug 2009 09:30:51 GMT

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