Proc freq - calculating percentages AFTER excluding certain counts

[howdelicious]

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!

[Murray_Court · Accepted solution]

SQL is your friend here.

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.

[TomKari]

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

[howdelicious]

Thanks for the help! I tried both methods and they both worked For my purposes i am using a proc sql, but E.G. task solution is pretty neat too