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.
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;
proc freq data=have;
tables group * indicator / noprint out=counts;
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.