Desktop productivity for business analysts and programmers

Proc freq - calculating percentages AFTER excluding certain counts

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Proc freq - calculating percentages AFTER excluding certain counts

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

IDAgeGender
10M
22F
35F
44F
51M
64M
71M
81F
92F
102M
112M
123F
134M
145M
156F

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

AgeFM
010
112
222
310
412
511
610

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:

AgeFM
522

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

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.

View solution in original post


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

Re: Proc freq - calculating percentages AFTER excluding certain counts

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.

Trusted Advisor
Posts: 1,059

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

Re: Proc freq - calculating percentages AFTER excluding certain counts

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 260 views
  • 6 likes
  • 3 in conversation