topic Re: Calculating period return when one (or more) period is missing in SAS Data Management

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

ilikesas — Sun, 11 Jan 2015 21:14:12 GMT

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!

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

Haikuo — Sun, 11 Jan 2015 21:38:36 GMT

data have;

infile cards truncover;

input period value;

cards;

1 10

2 5

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;

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

naveen_srini — Mon, 12 Jan 2015 07:01:33 GMT

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;