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

Solved
Super Contributor
Posts: 441

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

Hi,

suppose I have the following data:

periodvalue
110
25
3
412

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!

Accepted Solutions
Solution
‎01-11-2015 04:38 PM
Posts: 3,156

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

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;

All Replies
Solution
‎01-11-2015 04:38 PM
Posts: 3,156

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

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;

Frequent Contributor
Posts: 115

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

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;

🔒 This topic is solved and locked.