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?
proc sql;
create table want as
select *
from have
group by permno,month
having count(*)>20;
quit;
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;
??
proc sql;
create table want as
select *
from have
group by permno,month
having count(*)>20;
quit;
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.
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
If I understood your question.
Change HAVING clause into :
having sum(dc=0)<19;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.