Calculating period return when one (or more) period is missing

[ilikesas]

Hi,

suppose I have the following data:

period	value
1	10
2	5
3
4	12

I would like to add a column of returns, so for example the return for period 2 = (5-10)/10 = -0.5 

For period 3 I don't have a value, so in this case the return for period 3 = "missing", and for period 4, its return should be (12-5)/5 = 1.4, that is, 5 is the value from period 2 because its the nearest period for which there is a value.

Thank you!

[Haikuo · Accepted solution]

data have;

     infile cards truncover;

     input period    value;

     cards;

1 10

2 5

3

4 12

;

data want;

     set have;

     retain temp;

     return=ifn(temp=., ., (value-temp)/temp);

     temp=coalesce(value, temp);

     drop temp;

run;

Update:

In SAS there ALMOST certain will be more than one solutions. So below is another one using Proc SQL, but not recommended for efficiency reason:

proc sql;

  create table want as

  select *, value/(select value from have where period < a.period and not missing(value) having period=max(period))-1 as return

  from have a;

quit;

[naveen_srini]

data have;

     infile cards truncover;

     input period    value;

     cards;
1 10
2 5
3
4 12
;

data want;
set have;
retain value1;
if value ne . then do;
value1=value;
return=(value-lag(value1))/lag(value1);
end;
drop value1;
run;