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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Hentiedp
Fluorite | Level 6
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;

View solution in original post

3 REPLIES 3
Hentiedp
Fluorite | Level 6
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;
Astounding
PROC Star

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.

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

look at the PCT_col variable.

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 36467 views
  • 6 likes
  • 4 in conversation