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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.