deleting obs with conditions

[Amalik]

Hi,

I have following type of data and now I want to clean data such that I my data retains only those observations of dr when frequency of 0 <= 4 in any given month. If frequency of dr=0 s greater than 4 I would like to delete that entire month from my data.

Fir example, in the following data I would want to delete the entire month of Apr-00 as count(0) is greater than 4 but would want to retain May-00 as count (0) is only 1

permno	month	dr
1	Apr-00	0
1	Apr-00	0
1	…	0
1	Apr-00	0
1	Apr-00	1.2
1	May-00	3.2
1	May-00	1.3
1	…	4.5
1	May-00	0

 Any suggestions?

[novinosrin · Accepted solution]

Please post a better and comprehensive sample so responders can test their codes. In your example, count of dr=0 is 4 and it is not greater than 4 for apr00

proc sql;
create table want as
select *
from have
group by permno, month;
having sum(dr=0)<=4;
quit;

[Amalik]

Thanks a bunch, it works.