Hi,
this should do the trick:
LIBNAME in "C:\Temp" access=readonly;
DATA h01;
length month 8;
SET in.have;
month=month(date);
RUN;
PROC SORT data=h01 out=h02; BY company_id month date; RUN;
LIBNAME in CLEAR;
*Add row number;
data h03;
length id 8;
set h02;
BY company_id month date;
if first.month then id=0;
id+1;
run;
PROC SQL;
*Merge reporting_date to all rows by company and month;
CREATE TABLE h04 AS
SELECT a.id,b.id as bid,a.company_id,month(a.date) as month, a.date,a.return
,b.reporting_date,b.event_window,b.gap,b.estimation_window,b.date as date0
FROM h03 a
LEFT JOIN h03(where=(not missing(reporting_date))) b
ON a.company_id eq b.company_id
AND month(a.date) eq month(b.date)
ORDER BY a.company_id,calculated month,a.date
;
*Check single assignment of reporting_date by company and month;
CREATE TABLE check0 AS
SELECT DISTINCT company_id,month,reporting_date
FROM h04
ORDER BY company_id,month,reporting_date
;
QUIT;
DATA _NULL_;
set check0;
BY company_id month reporting_date;
if not first.month and last.month then put 'E' 'RROR: several reporting dates by company and month found' company_id= month= reporting_date=;
RUN;
*clean up;
PROC DATASETS lib=work nolist; delete check0; RUN;QUIT;
PROC FORMAT;
value period
1='Estimation window'
2='Gap'
3.1='Event date -1'
3.2='Event date'
3.3='Event date +1'
;
RUN;
DATA h05;
SET h04;
length period rangelo rangehi 8;
*Determine period for Estimation window;
rangelo=bid-1-gap-estimation_window;
rangehi=bid+1+gap+estimation_window;
if rangelo<=id<=rangehi then period=1;
*Determine period for Gap;
rangelo=bid-1-gap;
rangehi=bid+1+gap;
if rangelo<=id<=rangehi then period=2;
*Determine period for Event;
rangelo=bid-1;
rangehi=bid+1;
if rangelo=id then period=3.1;
else if id=bid then period=3.2;
else if rangehi=id then period=3.3;
drop range:;
format period period.;
RUN;
PROC SQL;
*Calculate avg;
CREATE TABLE h06_avg_ew AS
SELECT company_id,month,period,avg(return) as avg_ew
FROM h05
WHERE period eq 1
GROUP BY company_id,month,period
;
*Merge avg to corresponding event period;
CREATE TABLE h07 AS
SELECT a.*,b.avg_ew
FROM h05 a
LEFT JOIN h06_avg_ew b
ON a.company_id eq b.company_id
AND a.month eq b.month
AND floor(a.period) eq 3
ORDER BY company_id,month,date
;
QUIT;
*Calculate results;
DATA h08;
set h07;
where floor(period) eq 3;
by company_id month date;
length result 8 periodc $50;
if not missing(avg_ew) then result=return/avg_ew;
periodc=vvalue(period);
keep company_id month date0 period: result;
RUN;
*Transpose name and label variables;
PROC TRANSPOSE data=h08 out=h09(drop=_name_) prefix=period ;
by company_id month date0 ;
var result;
id period;
idlabel periodc;
format period best.;
RUN;
DATA want;
set h09;
rename date0=date;
keep company_id date0 period:;
RUN;
PROC DATASETS lib=work nolist; delete h0:; RUN;QUIT;
... View more
