You can use Proc SQL and a self referencing join, where you compute the date of the previous quarter using the INTNX function.
Based on the information returned, you can fill the new variables with values from the previous quarter.
See here an example:
data have;
infile cards dlm=",";
seqNr + 1;
input
date : mmddyy10.
fund_id : $8.
stock_id : $16.
number : 8.
price :8.
;
format date date9.;
cards;
3/31/2005,A,8916108,500,45
3/31/2005,A,226406106,20000,13.73
3/31/2005,A,25243Q205,9000,56.9
3/31/2005,A,292505104,15000,70.42
3/31/2005,A,633067103,4000,43.305
3/31/2005,A,767204100,1500,129.75
3/31/2005,A,874039100,700,32
3/31/2005,A,5458954,900,85
3/31/2005,A,874039100,1000,100
6/30/2005,A,25243Q205,2000,120
6/30/2005,A,292505104,500,150
6/30/2005,A,87403645,400,12
6/30/2005,A,645646,3000,11
6/30/2005,A,5454545,1200,13
3/31/2005,B,633067103,5000,43.305
3/31/2005,B,767204100,1500,129.75
3/31/2005,B,874039100,700,15
3/31/2005,B,77778,900,20
3/31/2005,B,121212,1000,40
3/31/2005,B,131313,2000,88
6/30/2005,B,44456,3200,45
6/30/2005,B,767204100,10000,36
6/30/2005,B,874039100,3333,100
6/30/2005,B,466666,20,14
;
proc sql feedback;
select
A.seqNr
, A.date
, A.fund_id
, A.stock_id
, A.number
, A.price
, B.seqNr
, B.date
, B.fund_id
, B.stock_id
, B.number
, B.price
, case
when b.stock_id not is missing then b.number else 0
end as previousNumner
, case
when b.stock_id not is missing then b.price else 0
end as previousprice
from
have as a
left outer join
have as b
on
a.fund_id = b.fund_ID
and a.stock_id = b.stock_id
and b.date = intnx("qtr", a.date, -1, "E")
order by
a.date
, a.fund_id
, a.stock_id
;
quit;
... View more
