Rolling cumulative Earnings for 4 quarters for different CUSIP

janed — Tue, 05 Mar 2019 05:03:04 GMT

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.

Re: Rolling cumulative Earnings for 4 quarters for different CUSIP

Astounding — Tue, 05 Mar 2019 07:24:08 GMT

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.

topic Re: Rolling cumulative Earnings for 4 quarters for different CUSIP in SAS Programming

Rolling cumulative Earnings for 4 quarters for different CUSIP

Re: Rolling cumulative Earnings for 4 quarters for different CUSIP