BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tradepeter
Calcite | Level 5

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

8 REPLIES 8
Tom
Super User Tom
Super User

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
Opal | Level 21

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

PG
Reeza
Super User

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__
Jade | Level 19

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
PROC Star

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
SAS Super FREQ

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
Rhodochrosite | Level 12

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
Calcite | Level 5

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 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 14529 views
  • 6 likes
  • 8 in conversation