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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: