How to rename total count across class variable in Proc Means

1
votes

I'm doing a simple count of occurrences of a by-variable within a class variable, but cannot find a way to rename the total count across class variables. At the moment, the output dataset includes counts for all cluster2 within each group as well as the total count across all groups (i.e. the class variable used). However, the counts within classes are named, while the total is shown by an empty string.

Code: 

proc means data=seeds noprint;
   class group;
   by cluster2;
   id label2;
   output out=seeds_counts (drop= _type_ _freq_) n(id)=count;
run;

Example of output file:

cluster2   group    label2    count
7                   area 1    20
7          sa       area 1    15
7          sb       area 1    5
15                  area 15   42
15         sa       area 15   18
....

Naturally, renaming the emtpy string to "Total" could be accomplished in a separate datastep, but I would like to do it directly in the Proc Means-step. It should be simple and trivial, but I haven't found a way so far. Afterwards, I want to transpose the dataset, which means that the emtpy string has to be changed, or it will be dropped in the proc transpose.

3
sasgrouping
If you want the total as a summary table use PROC TABULATE instead, not PROC MEANS. – Reeza
@Reeza I want both the total as well as counts of individual groups in the same table, to later transpose it (getting sort of the same result as a pivoted excel table). Can this be done using Proc Tabulate? I'm not very familiar with that proceedure. – fileunderwater
This is exactly what PROC TABULATE is designed for. I would recommend a paper but can't find a good one. There's a lot here though, lexjansen.com/search/searchresults.php?q=proc%20tabulate – Reeza
Sounds like a reporting need, so use a reporting procedure like TABULATE or REPORT. Or just code the report you need using data steps. – Tom

3 Answers

1
votes

I don't know of a way to do it directly, but you can sort-of-cheat: you can tell SAS to show "Total" instead of missing.

proc format;
  value $MissTotalF
  ' ' = 'Total'
  other = [$CHAR12.];
quit;

proc means data=sashelp.class noprint;
   class sex;
   id age;
   output out=sex_counts (drop= _type_ _freq_) n(age)=count;
  format sex $MissTotalF.;
run;

For example. I'd also recommend using PROC TABULATE instead of PROC MEANS if you're just going for counts, though in this case it doesn't really make much difference.

1
votes

The problem here is that if the variable in the class statement is numeric, then the resultant column will be numeric, therefore you can't add the word Total (unless you use a format, similar to the answer from @Joe). This will be why the value is missing, as the class variable can be either numeric or character.

Here's an example of a numeric class variable.

proc sort data=sashelp.class out=class;
by sex;
run;

proc means data=class noprint;
class age;
by sex;
output out=class_counts (drop= _:) n=count;
run;

Using proc tabulate can display the result pretty much how you want it, however the output dataset will have the same missing values, so won't really help. Here's a couple of examples.

proc tabulate data=class out=class_tabulate1 (drop=_:);
class sex age;
table sex*(age all='Total'),n='';
run;

proc tabulate data=class out=class_tabulate2 (drop=_:);
class sex age;
table sex,age*n='' all='Total';
run;

I think the best option to achieve your final goal is to add the nway option to proc means, which will remove the subtotals, then transpose the data and finally write a data step that creates the Total column by summing each row. It's 3 steps, but doesn't involve much coding.

0
votes

Here is one method you could use by taking advantage of the _TYPE_ variable so that you can process the totals and details separately. You will still have trouble with PROC TRANSPOSE if there is a class with missing values (separate from the overall summary record).

proc means data=sashelp.class noprint;
   class sex;
   id age;
   output out=sex_counts (drop= _freq_ ) n(age)=count;
run;
proc transpose data=sex_counts out=transpose prefix=count_ ;
  where _type_=1 ;
  id sex ;
  var count;
run;
data transpose ;
 merge transpose sex_counts(where=(_type_=0) keep=_type_ count);
 rename count=count_Total;
 drop _type_;
run;