DATA Step, Macro, Functions and more

Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Hi guys,

I am sorting stock market data. Part of the process is to eliminate stocks that do not have continuous past 22 days daily returns data in a particular month. How can i code this down? I only wanna stay with stocks with that have data for continuous 22 days for every month. Please find attached data file

 

 


Accepted Solutions
Solution
‎10-22-2017 07:03 AM
Super User
Posts: 10,044

Re: Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Posted in reply to ivanpersie

I checked your data, none of stock satisfy your condition.

 

proc import datafile='c:\temp\Forum_RI.csv' out=temp dbms=csv replace;
run;

proc sort data=temp(where=(date is not missing)) out=have;
 by stock date;
run;

data have1;
 set have;
 by stock date;
 yymm=intnx('month',date,0);
 if first.date;
 format yymm yymmn6.;
run;

data have2;
 set have1;
 by stock;
 dif=dif(date);
 if first.stock then dif=1;
run;
/*
proc sql;
create table have3 as
 select *
  from have2
   group by stock,yymm
    having sum(dif ne 1)=0;
quit;
*/

View solution in original post


All Replies
Super User
Posts: 19,861

Re: Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Posted in reply to ivanpersie

Try PROC TIMESERIES

PROC Star
Posts: 1,760

Re: Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Posted in reply to ivanpersie

Give a small program to generate the sample data next time.

 

Like this?

 

data HAVE;
  do DATE=1 to 1000;
    do STOCKNO=1 to 200;
      VAL=( ranuni(0)<.96 );
      output;
     end;
  end;
  format DATE date9.; 
run;
proc sort data=HAVE out=SORTED;
  by STOCKNO DATE;
run;
data MARK_VALID; 
  set SORTED;
  by STOCKNO ;
  if STOCKNO=lag(STOCKNO) and month(DATE)=month(lag(DATE)) and VAL then VALID+1;
  else VALID=0;
  if VALID=22;
run;
proc sql; 
  create table WANT as
  select a.* 
  from SORTED       a
         inner join
       MARK_VALID   b
          on  a.STOCKNO    =  b.STOCKNO
          and put(a.DATE,monyy.)=  put(b.DATE,monyy.)
  order by STOCKNO, DATE;
quit;
  

 

 

Contributor
Posts: 27

Re: Eliminate stocks that do not have continuous past 22 days returns data in a particular month

HI @ChrisNZ Your code was helpful. Thanks a bunch.

Solution
‎10-22-2017 07:03 AM
Super User
Posts: 10,044

Re: Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Posted in reply to ivanpersie

I checked your data, none of stock satisfy your condition.

 

proc import datafile='c:\temp\Forum_RI.csv' out=temp dbms=csv replace;
run;

proc sort data=temp(where=(date is not missing)) out=have;
 by stock date;
run;

data have1;
 set have;
 by stock date;
 yymm=intnx('month',date,0);
 if first.date;
 format yymm yymmn6.;
run;

data have2;
 set have1;
 by stock;
 dif=dif(date);
 if first.stock then dif=1;
run;
/*
proc sql;
create table have3 as
 select *
  from have2
   group by stock,yymm
    having sum(dif ne 1)=0;
quit;
*/
Contributor
Posts: 27

Re: Eliminate stocks that do not have continuous past 22 days returns data in a particular month

Hi Ksharp. Thanks for your help. I really appreciate. Actually the data i gave you was a sample, so that's why probably it did not return data. Otherwise with the full data i have here, it all working. Thanks again.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 123 views
  • 1 like
  • 4 in conversation