topic Re: How to subset / filter data based on frequency of a value within a variable in New SAS User

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

bretthouston — Thu, 18 Apr 2019 16:46:10 GMT

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!

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

novinosrin — Thu, 18 Apr 2019 16:54:19 GMT

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;

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

Reeza — Thu, 18 Apr 2019 16:54:26 GMT

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

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

novinosrin — Thu, 18 Apr 2019 16:55:12 GMT

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

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

bretthouston — Thu, 18 Apr 2019 18:10:48 GMT

Thanks this worked perfectly!