topic Re: Retain Column Percentage for Proc Freq Output Table with Three Variables in SAS Procedures

Retain Column Percentage for Proc Freq Output Table with Three Variables

jacob_klimek — Wed, 01 Jun 2016 12:54:03 GMT

Hello,

I am trying to output a table from a proc freq based on a grouping variable, a binary indicator, and the year. The output automatically stores the three variable values and the count for each, but I would also like the column (or row) percent to also be outputted because eventually this will be made into a formatted table in excel where I need those.

The data that the proc freq is being done on looks like this.

id group indicator year

11 1 0 2012

11 1 1 2013

16 1 0 2012

18 1 1 2012

22 5 1 2011

The output table currently looks like:

group indicator year count

1 0 2012 2

1 1 2012 1

1 1 2013 1

5 1 2011 1

And I want the column percent added from the proc freq printed output:

group indicator year count _column_pct_

1 0 2012 2 66.67

1 1 2012 1 33.33

1 1 2013 1 100

5 1 2011 1 100

The trouble I am having is typically when I do these proc freqs with only two tables it automatically outputs the column percentage, but when I try 3 variables it doesn't do it. I still want the same column percent that is being shown in the results window though.

Thanks.

Re: Retain Column Percentage for Proc Freq Output Table with Three Variables

Hentiedp — Wed, 01 Jun 2016 13:26:21 GMT

Hi,

You can give this a go... might help.

proc freq data=sashelp.class;;
table age*sex / out=test TOTPCT OUTPCT list;
run;

data test1;
set test;
by age notsorted;
retain pct .;
if first.age then
pct = 0;

pct + PCT_ROW;
run;

Re: Retain Column Percentage for Proc Freq Output Table with Three Variables

Astounding — Wed, 01 Jun 2016 13:30:28 GMT

If all else fails, here's a workaround. It looks like you're using a three-way table now, something like:

tables group * year * indicator;

Instead, sort the data and use a two-way table:

proc sort data=have;

by year;

run;

proc freq data=have;

by year;

tables group * indicator / noprint out=counts;

run;

The output data set COUNTS should contain the same COUNT values, but PERCENT would re-set for each year. Using the BY statement changes PERCENT to be the values that you are looking for. And of course you need to re-sort the data afterwards to get the order that you are looking for ... hope your data set is small.

Re: Retain Column Percentage for Proc Freq Output Table with Three Variables

ballardw — Wed, 01 Jun 2016 15:09:48 GMT

proc freq data=have noprint;
   tables group*indicator*year/ out=test TOTPCT OUTPCT list;
run;

look at the PCT_col variable.