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
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;
*/
Try PROC TIMESERIES
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;
HI @ChrisNZ Your code was helpful. Thanks a bunch.
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;
*/
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.