Rolling cumulative Earnings for 4 quarters for different CUSIP

[janed]

Hello,

I'd like to calculate rolling cumulative 4 quarters of earnings (for different cusip).

Currently this is the code I use:

%macro mom(datain=); 
%do iter=1 %to 3; 
proc sort data=&datain; by cusip line; run; 
data &datain; set &datain; by cusip; 
if &iter<=1 then C2E=C2E+lag(epspiq); 
if &iter<=2 then C3E=C3E+lag&iter(epspiq); 
if &iter<=3 then C4E=C4E+lag&iter(epspiq); 
run; 
%end; 
%mend; 
%mom(datain=com);

It works well for the first cusip, however since the second cusip, it takes the lag of earnings from the previous cusip, which is not correct.

Please kindly advise. 

I'm fine to use other procedures and not only macro procedure.

[Astounding]

Let's see if a non macro solution generates what you are seeking. After sorting:

data want ;
set have;
by cusip;
if first.cusip then do;
N=1;
RE =0;
end;
else N+1;
back5 = lag5(epspiq);
RE + epspiq;
if N > 4 then RE = RE - back5;
run;

See how this compares with your expectations. It might be an exact match, a rolling sum of earnings for the most recent 4 quarters.