topic Re: stack statistics of multiple variables in the same table in ODS and Base Reporting

stack statistics of multiple variables in the same table

tradepeter — Wed, 11 Apr 2012 14:26:14 GMT

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,

Re: stack statistics of multiple variables in the same table

Tom — Wed, 11 Apr 2012 15:11:42 GMT

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;

Re: stack statistics of multiple variables in the same table

PGStats — Wed, 11 Apr 2012 16:02:10 GMT

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;

Re: stack statistics of multiple variables in the same table

data_null__ — Wed, 11 Apr 2012 17:15:39 GMT

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

Re: stack statistics of multiple variables in the same table

Reeza — Wed, 11 Apr 2012 17:45:45 GMT

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;

Re: stack statistics of multiple variables in the same table

Astounding — Wed, 11 Apr 2012 18:00:59 GMT

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?

Re: stack statistics of multiple variables in the same table

Cynthia_sas — Thu, 12 Apr 2012 13:27:31 GMT

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

Re: stack statistics of multiple variables in the same table

Peter_C — Fri, 13 Apr 2012 12:49:15 GMT

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)

Re: stack statistics of multiple variables in the same table

tradepeter — Fri, 13 Apr 2012 22:47:51 GMT

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