Proc freq - calculating percentages AFTER excluding certain counts

Solved
Occasional Contributor
Posts: 6

Proc freq - calculating percentages AFTER excluding certain counts

Hi, suppose if I have a dataset, that looks like the one below:

 ID Age Gender 1 0 M 2 2 F 3 5 F 4 4 F 5 1 M 6 4 M 7 1 M 8 1 F 9 2 F 10 2 M 11 2 M 12 3 F 13 4 M 14 5 M 15 6 F

The proc-freq output for Age * Gender is below:

 Age F M 0 1 0 1 1 2 2 2 2 3 1 0 4 1 2 5 1 1 6 1 0

I want to modify the proc freq step so that:

1. It exclude rows (i.e. age) where either male or female has a count of 1 or less, so that the proc freq will look as below:

 Age F M 5 2 2

2. AFTER excluding rows where counts <= 1, then it outputs the row percentages using PCT_ROW.

Is there any way I can do that in proc freq?

Thanks very much!

Accepted Solutions
Solution
‎01-17-2016 07:33 PM
Contributor
Posts: 45

Re: Proc freq - calculating percentages AFTER excluding certain counts

Pro sql;

create table want as select

age, sum(case when gender="M" then 1 else 0 end) as M,  sum(case when gender="F" then 1 else 0 end) as F

from have

group by age

having (M ge 1 or F ge 1)

;

quit;

Apologies for possible syntax errors as I am at home.

All Replies
Solution
‎01-17-2016 07:33 PM
Contributor
Posts: 45

Re: Proc freq - calculating percentages AFTER excluding certain counts

Pro sql;

create table want as select

age, sum(case when gender="M" then 1 else 0 end) as M,  sum(case when gender="F" then 1 else 0 end) as F

from have

group by age

having (M ge 1 or F ge 1)

;

quit;

Apologies for possible syntax errors as I am at home.

PROC Star
Posts: 1,332

Re: Proc freq - calculating percentages AFTER excluding certain counts

If you're looking for an all-EG Task solution, you can use Summary Statistics to create a summary of all the data (in effect, your first table), use a Query to eliminate the rows you don't want, and then Summary Tables to present the results and percentage.

Tom

Occasional Contributor
Posts: 6