Hello,
I have a dataset looks like:
id | date | return | report date | avg. return |
1000 | 1/1/2000 | 1.1 | 1/5/2000 | 1.7 |
1000 | 1/2/2000 | 1.2 | 1/5/2000 | 1.7 |
1000 | 1/3/2000 | 1.3 | 1/5/2000 | 1.7 |
1000 | 1/4/2000 | 1.4 | 1/5/2000 | 1.7 |
1000 | 1/5/2000 | 1.5 | 1/5/2000 | 1.7 |
1000 | 1/6/2000 | 1.6 | 1/5/2000 | 1.7 |
1000 | 1/7/2000 | 1.7 | 1/5/2000 | 1.7 |
1000 | 1/8/2000 | 1.8 | 1/5/2000 | 1.7 |
1001 | 1/1/2000 | 1.9 | 1/3/2000 | 2.3 |
1001 | 1/2/2000 | 2 | 1/3/2000 | 2.3 |
1001 | 1/3/2000 | 2.1 | 1/3/2000 | 2.3 |
1001 | 1/4/2000 | 2.2 | 1/3/2000 | 2.3 |
1001 | 1/5/2000 | 2.3 | 1/3/2000 | 2.3 |
1001 | 1/6/2000 | 2.4 | 1/3/2000 | 2.3 |
1001 | 1/7/2000 | 2.5 | 1/3/2000 | 2.3 |
1002 | 1/1/2000 | 2.6 | 1/4/2000 | 3.05 |
1002 | 1/2/2000 | 2.7 | 1/4/2000 | 3.05 |
1002 | 1/3/2000 | 2.8 | 1/4/2000 | 3.05 |
1002 | 1/4/2000 | 2.9 | 1/4/2000 | 3.05 |
1002 | 1/5/2000 | 3 | 1/4/2000 | 3.05 |
1002 | 1/6/2000 | 3.1 | 1/4/2000 | 3.05 |
What I need is to select a number of returns for each id based on the report dates. The returns selected are those within a 3-day window since the report date. After the selection, I want to calculate the average of the selected returns and output results as avg. return. I also want the sql query has certain flexibility that allows me to quickly specify other n-day windows, such as a 20-day window with 10 days prior to the report date and 10 days after.
I wrote a preliminary code but it seems not work as I intended:
%let begdate = 0;
%let enddate = 5;
proc sql; create table want as
select a.*, mean(b.return) as avgret
from have as a left join
have as b
on a.id=b.id and a.date=b.date
where b.date between intnx('WEEKDAY', rptdate, &begdate) and intnx('WEEKDAY', rptdate, &enddate)
order by a.permno,a.date;
quit;
I appreciate your help on how to achieve this. Thank you.
No need for a join here:
data have;
input id date :mmddyy. return rptDate :mmddyy. avgRet;
datalines;
1000 1/1/2000 1.1 1/5/2000 1.7
1000 1/2/2000 1.2 1/5/2000 1.7
1000 1/3/2000 1.3 1/5/2000 1.7
1000 1/4/2000 1.4 1/5/2000 1.7
1000 1/5/2000 1.5 1/5/2000 1.7
1000 1/6/2000 1.6 1/5/2000 1.7
1000 1/7/2000 1.7 1/5/2000 1.7
1000 1/8/2000 1.8 1/5/2000 1.7
1001 1/1/2000 1.9 1/3/2000 2.3
1001 1/2/2000 2 1/3/2000 2.3
1001 1/3/2000 2.1 1/3/2000 2.3
1001 1/4/2000 2.2 1/3/2000 2.3
1001 1/5/2000 2.3 1/3/2000 2.3
1001 1/6/2000 2.4 1/3/2000 2.3
1001 1/7/2000 2.5 1/3/2000 2.3
1002 1/1/2000 2.6 1/4/2000 3.05
1002 1/2/2000 2.7 1/4/2000 3.05
1002 1/3/2000 2.8 1/4/2000 3.05
1002 1/4/2000 2.9 1/4/2000 3.05
1002 1/5/2000 3 1/4/2000 3.05
1002 1/6/2000 3.1 1/4/2000 3.05
;
%let priorDays = 3;
%let afterDays = 0;
proc sql;
create table want as
select
id,
rptDate format=yymmdd10.,
mean(return) as avgRet
from have
where intck("weekday", date, rptDate) between -&priorDays. and &afterDays.
group by id, rptDate;
quit;
You didn't specify which day of the interval you wanted INTNX() to generate.
Options as BEGINNING, END, or SAME.
Default is BEGINNING.
I assume you want the 'sameday' argument in the INTNX function like this?
data have;
input id $ date:mmddyy10. return reportdate:mmddyy10. avgreturn;
format date reportdate mmddyy10.;
datalines;
1000 1/1/2000 1.1 1/5/2000 1.7
1000 1/2/2000 1.2 1/5/2000 1.7
1000 1/3/2000 1.3 1/5/2000 1.7
1000 1/4/2000 1.4 1/5/2000 1.7
1000 1/5/2000 1.5 1/5/2000 1.7
1000 1/6/2000 1.6 1/5/2000 1.7
1000 1/7/2000 1.7 1/5/2000 1.7
1000 1/8/2000 1.8 1/5/2000 1.7
1001 1/1/2000 1.9 1/3/2000 2.3
1001 1/2/2000 2 1/3/2000 2.3
1001 1/3/2000 2.1 1/3/2000 2.3
1001 1/4/2000 2.2 1/3/2000 2.3
1001 1/5/2000 2.3 1/3/2000 2.3
1001 1/6/2000 2.4 1/3/2000 2.3
1001 1/7/2000 2.5 1/3/2000 2.3
1002 1/1/2000 2.6 1/4/2000 3.05
1002 1/2/2000 2.7 1/4/2000 3.05
1002 1/3/2000 2.8 1/4/2000 3.05
1002 1/4/2000 2.9 1/4/2000 3.05
1002 1/5/2000 3 1/4/2000 3.05
1002 1/6/2000 3.1 1/4/2000 3.05
;
%let begdate = 0;
%let enddate = 5;
proc sql; create table want as
select a.*, mean(b.return) as avgret
from have as a left join
have as b
on a.id=b.id and a.date=b.date
where b.date between intnx('WEEKDAY', a.reportdate, &begdate., 's') and intnx('WEEKDAY', a.reportdate, &enddate., 's')
order by a.id,a.date;
quit;
No need for a join here:
data have;
input id date :mmddyy. return rptDate :mmddyy. avgRet;
datalines;
1000 1/1/2000 1.1 1/5/2000 1.7
1000 1/2/2000 1.2 1/5/2000 1.7
1000 1/3/2000 1.3 1/5/2000 1.7
1000 1/4/2000 1.4 1/5/2000 1.7
1000 1/5/2000 1.5 1/5/2000 1.7
1000 1/6/2000 1.6 1/5/2000 1.7
1000 1/7/2000 1.7 1/5/2000 1.7
1000 1/8/2000 1.8 1/5/2000 1.7
1001 1/1/2000 1.9 1/3/2000 2.3
1001 1/2/2000 2 1/3/2000 2.3
1001 1/3/2000 2.1 1/3/2000 2.3
1001 1/4/2000 2.2 1/3/2000 2.3
1001 1/5/2000 2.3 1/3/2000 2.3
1001 1/6/2000 2.4 1/3/2000 2.3
1001 1/7/2000 2.5 1/3/2000 2.3
1002 1/1/2000 2.6 1/4/2000 3.05
1002 1/2/2000 2.7 1/4/2000 3.05
1002 1/3/2000 2.8 1/4/2000 3.05
1002 1/4/2000 2.9 1/4/2000 3.05
1002 1/5/2000 3 1/4/2000 3.05
1002 1/6/2000 3.1 1/4/2000 3.05
;
%let priorDays = 3;
%let afterDays = 0;
proc sql;
create table want as
select
id,
rptDate format=yymmdd10.,
mean(return) as avgRet
from have
where intck("weekday", date, rptDate) between -&priorDays. and &afterDays.
group by id, rptDate;
quit;
Thank you!
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.