topic Re: deleting obs with conditions in SAS Programming

deleting obs with conditions

Amalik — Fri, 20 Jul 2018 23:06:33 GMT

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?

Re: deleting obs with conditions

novinosrin — Fri, 20 Jul 2018 23:20:13 GMT

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;

Re: deleting obs with conditions

Amalik — Fri, 20 Jul 2018 23:34:45 GMT

Thanks a bunch, it works.