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;
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.