stack statistics of multiple variables in the same table

[tradepeter]

Hello all:

  In a project, I hope to create one summary table for multiple variables. For example, assume the dataset contains information about patients' gender, agegroup and disease:

id  gender  agegroup   disease

1       0              1                3

......

The coding would be 0-male, 1-female and 0-children, 1-adults etc al. I can easily produce a summary table for each one of those variables seperately. But what I need is to stack all of them together in the same table like

variable        value       N   percentage

gender         male       50     50%

                     female    50     50%

agegroup    children   32     32%

                     adult        68      68%

......

However, I cannot come up with an easy solution with any of proc report, tabulate or freq. I could run multiple proc report/freq, output summaries to individual datasets and merge them together. But the code will be very long for many variables. Also, procs always output original values instead of the format (e.g output 0 for gender instead of male for gender) so I cannot merge them first and apply format later.

Any help would be greatly appreciated.

Thanks,

[Tom · Accepted solution]

You could use the OneWayFreqs ODS output table from PROC FREQ.  But you will need to move the formatted values from the F_... variables back into a single column.

ods output onewayfreqs=freqout;

ods listing off;

proc freq data=sashelp.class;

tables sex age;

run;

ods listing;

data want ;

  length var $32 fvalue $50 ;

  set freqout;

  var=scan(table,-1);

  fvalue=vvaluex('F_'||var);

  keep var fvalue frequency percent;

run;

proc print width=min; run;

[PGStats]

I would do something along the lines of :

/* Create formats with the same names as the variables */
proc format;
value gender
0 = "male"
1 = "female";
value agegroup
0 = "children"
1 = "adults";
value disease ...
run;

proc transpose data=have out=temp0 name=variable;
by Id notsorted;
var gender agegroup disease;
run;

proc sort data=temp0; by variable;

proc freq data=temp0;
by variable;
table col1 / out=temp1;
run;

/* translate values into formatted values */
data want(drop=col1);
set temp1;
value = putc(col1, variable);
run;

PG

[Reeza]

This is a great example for a simple macro.

Here's a basic example, hope it helps.

The paper mentioned by data_null_ looks very useful as well...

%macro summary_table(dsetin, varlist, dsetout);

proc datasets nodetails nolist;

    delete &dsetout;

quit;

*loop through variable list;

%let i=1;

%do %while (%scan(&varlist, &i, " ") ^=%str());

%let var=%scan(&varlist, &i, " ");   

%put &i &var;

    *Cross tab;

    proc freq data=&dsetin noprint;

    table &var/ out=temp1;

    run;

    *Get variable label as name;

    data _null_;

        set &dsetin (obs=1);

        call symput('var_name', vlabel(&var.));

    run;

    %put &var_name;

    *Add in Variable name and store the levels as a text field;

    data temp2;

        keep variable value count percent;

        Variable = "&var_name";

        set temp1;

        value=input(&var, $50.);

        percent=percent/100; * I like to store these as decimals instead of numbers;

        format percent percent8.1;

        drop &var.;

    run;

    %put &var_name;

    *Append datasets;

    proc append data=temp2 base=&dsetout force;

    run;

    /*drop temp tables so theres no accidents*/

    proc datasets nodetails nolist;

        delete temp1 temp2;

    quit;

*Increment counter;

%let i=%eval(&i+1);

%end;

%mend;

*Call macro with the variables in mind;

%summary_table(sashelp.class, sex age, summary1);

*Report the results;

proc report data=summary1 nowd;

    column variable value count percent;

    define variable /group 'Variable';

    define value / format=$8. 'Value';

    define count/'N';

    define percent/'Percentage %';

run;

[data_null__]

The paper addresses the general topic of stacked variables summaries, you might find it helpful.  

http://www.lexjansen.com/mwsug/2011/pharma/MWSUG-2011-PH02.pdf

[Astounding]

Depending on your requirements. a simple PROC TABULATE may do the job.  How are your requirements different than this:

proc format;

value gender 0='male' 1='female';

run;

proc tabulate data=have;

   class gender agegrp;

   tables gender agegrp, N PctN;
   format gender gender.;

run;

I realize you may have lots of answers, and really want to change the output.  But how far apart is this from what you need?

[Cynthia_sas]

Many times, I see demographic reports like this where a slightly different requirement is to put the N and PCTN in the same cell, like: 99 (99.9%) or the min and max joined by a dash (11-16). I show how to do this in PROC REPORT and in a custom table template in my "Creating Complex Reports" paper (http://www2.sas.com/proceedings/forum2008/173-2008.pdf). There's no actual stacking of statistics in a cell -- it's either concatenating 2 numeric variables into a character string and/or using the JOURNAL style to disappear interior table lines.

cynthia

[Peter_C]

The result set looks like multiple queries are appended.

do it with proc means/freq, data step, proc append in a loop

alternatively use proc sql (the only macro) loop, over the grouped select and insert statements

(looping for each classing variable).

When inserting/appending, ensure the class_value column is wide enough for the widest class value, and make suitable conversion of numeric-type class values to character (dates! especially)

(I'm doing quite a lot of this in teradata sql and apppreciate having SAS to automate the class-variable loop)

[tradepeter]

Hi Tom: Thank you for the reply.Your example works. Just two questions,

1. Do you mean 'ods listing close;' instead of 'ods listing close;'

2. The variable values (gender in your example) and associated "F-" values  from proc freq are always the same. I was thinking 'F-' variable contains the formatted value and the one without "F" contains whatever values in the table. What exactly F-variables are supposed to equal?

Thanks,

Peter