"ALL"-Option for BY (SAS PROC TABULATE)

Question

ODS EXCEL FILE="/mypoath/myfile.xlsx" options(
    frozen_headers="3"
    sheet_name="#byval1");
 PROC TABULATE data=out;
  BY byVariable; 
  CLASS varA varB;
  TABLES varA, varB;
 RUN;
ODS EXCEL CLOSE;

The code above creates an excel-file with different sheets. There is one sheet for each value of the variable byVariable. Is there a way to create an additional sheet "ALL" which contains the results for all values of the byVariable together? I mean something like "ALL" (used in the TABLES-section). I tried BY ALL byVar already (which doesn't work). Thanks for help!

score 1 · Accepted Answer · answered Oct 31 '19 at 13:08

The simple answer is NO. If you want all of the data then don't use the BY statement.

ODS EXCEL FILE="/mypoath/myfile.xlsx" options(frozen_headers="3");
ODS EXCEL options(sheet_name="ALL");
PROC TABULATE data=out;
  CLASS varA varB;
  TABLES varA, varB;
RUN;
ODS EXCEL options(sheet_name="#byval1");
PROC TABULATE data=out;
  BY byVariable; 
  CLASS varA varB;
  TABLES varA, varB;
RUN;
ODS EXCEL CLOSE;

score 1 · Answer 2 · answered Oct 31 '19 at 13:09

There is no such option.

You can:

rerun the report without BY, or
stack the data on itself modifying the by variable to be ALL -- such that it is higher than all existant by values.

data stacked / view=stacked;
  set 
    have
    have (in=stackflag)
  ;

  if stackflag then do;
    byvar = 'A0'x || 'ALL';        * A0 forces value to be 'after' other original byVar values;
  end
run;

proc tabulate data=stacked;
  by byvar; 
  …

Note: 'A0'x is a hard space ASCII character

"ALL"-Option for BY (SAS PROC TABULATE)

2 Answers2