Need help on an SQL query

[Zerg]

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.

[Tom]

You didn't specify which day of the interval you wanted INTNX() to generate. 

Options as BEGINNING, END, or SAME.

Default is BEGINNING.

[PeterClemmensen]

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;

[PGStats · Accepted solution]

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;

[Zerg]

Thank you!