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

[ivanpersie]

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

[Reeza]

Try PROC TIMESERIES

[ChrisNZ]

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;
  

[ivanpersie]

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

[Ksharp · Accepted solution]

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;
*/

[ivanpersie]

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.