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?
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;
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;
Thanks a bunch, it works.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.