Solved
Contributor
Posts: 27

# 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,691

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

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

All Replies
Super User
Posts: 23,323

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

Try PROC TIMESERIES

PROC Star
Posts: 2,319

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

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

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

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

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