DATA Step, Macro, Functions and more

Drop non-trading days where more than 50% stocks have zero returns

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Drop non-trading days where more than 50% stocks have zero returns

Hi Everyone,

 I want to delete non-trading days where more than 50% stocks have zero returns. For example 60% of the total stocks (3 out of 5) have zero returns for 2010-01-07. Therefore, I want to delete 2010-01-07 from the sample. I have millions of observations, so I would appreciate an efficient code. Please find the SAS data below.

 

Thanks a lot for your help.

 

Best,

 

Cheema

data have;
infile cards expandtabs truncover;
input stock date : yymmdd10. ret ;
format date yymmdd10.;
cards;
1	2010-01-07	0
1	2010-01-26	-0.02544
1	2010-01-29	0.03384
2	2010-01-07	0
2	2010-01-26	-0.06219
2	2010-01-29	0.01989
3	2010-01-07	0.02
3	2010-01-26	-0.04
3	2010-01-29	0.023
4	2010-01-07	0
4	2010-01-26	-0.02
4	2010-01-29	0.012
5	2010-01-07	0.01
5	2010-01-26	0.02
5	2010-01-29	-0.012
run;

Accepted Solutions
Solution
4 weeks ago
Esteemed Advisor
Posts: 5,482

Re: Drop non-trading days where more than 50% stocks have zero returns

If a stock can only be mentioned once on a given day, then the query can be simpler:

 

proc sql;
create table trading2 as
select *
from have
group by date
having 
    0.5 * count(*) >= sum(ret=0)
order by stock, date;
quit;
PG

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,482

Re: Drop non-trading days where more than 50% stocks have zero returns

Test this for efficiency:

 

proc sql;
create table trading as
select *
from have
group by date
having 
    0.5 * count(distinct stock) >= 
    count(distinct case when ret = 0 then stock else . end)
order by stock, date;
quit;

having an index on date should help a lot.

 

PG
Solution
4 weeks ago
Esteemed Advisor
Posts: 5,482

Re: Drop non-trading days where more than 50% stocks have zero returns

If a stock can only be mentioned once on a given day, then the query can be simpler:

 

proc sql;
create table trading2 as
select *
from have
group by date
having 
    0.5 * count(*) >= sum(ret=0)
order by stock, date;
quit;
PG
Contributor
Posts: 73

Re: Drop non-trading days where more than 50% stocks have zero returns

Thank you very much, it works very well Smiley Happy
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 106 views
  • 3 likes
  • 2 in conversation