data cleaning

[Amalik]

Hi,

My data looks like follows. Now, I want to delete a month if any month has say less than 20 observations. Can anyone suggest how to do that?

[pau13rown]

proc sort data=a;

by month;

run;

data b;

retain count;

set a;

by month;

if first.month then count=1;

else count=count+1;

if last.month then output;

keep month count;

run;

data c;

merge b a;

by month;

if count lt 20 then delete;

run;

??

[Ksharp · Accepted solution]

proc sql;

create table want as

 select *

 from have

 group by permno,month

 having count(*)>20;

quit;

[Amalik]

Hi Ksharp,

This is very helpful and resolves my question but can you also let me know how to obtain a data set if for example, the variable DR (second last from right) shows all values to be zero, or sometimes it would show only one value that is different than 0 and all others would be zero. So I would like to delete a month if the frequency of 0 is 19 or greater in any given month. 

[s_manoj]

proc sql;
create table want as
select *
from have
group by month,dr
having dr = 0 and count(dr) < 19;
quit;

I havent tested this code, but i think this might work

Regards
Manoj

[Ksharp]

If I understood your question.

Change HAVING clause into :

having sum(dc=0)<19;