How to subset / filter data based on frequency of a value within a variable

[bretthouston]

I am trying to subset data based on the frequency of a given value within a variable. I've attached a screen shot of a proc freq table, and essentially I would like to include all CCIDummy values where row percent UpdtxStatus 'yes' >/=5%. 

I tried the following code, but the '%' results in a syntax error:

proc sql;
create table TEST as
select *
from work.CombiningProceduresDummyVariable
group by CCIdummy
having (UpdtxStatus='yes')ge 5%;
quit;

Thanks in advance!

[novinosrin]

Hi @bretthouston   I think you may be looking to do this

proc sql;
create table TEST as
select *
from work.CombiningProceduresDummyVariable
group by CCIdummy
having sum(UpdtxStatus='yes')/n(CCIdummy) ge .05;
quit;

[Reeza]

Mean for percentage that's greater than 0.05, or the denominator should be the count of updtxstatus not 100.

[novinosrin · Accepted solution]

You are right, I edited 2 secs before yours came through. Good catch!

[bretthouston]

Thanks this worked perfectly!