SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

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
Respected Advisor
Posts: 3,124

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;

View solution in original post


All Replies
Solution
‎01-11-2015 04:38 PM
Respected Advisor
Posts: 3,124

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 339 views
  • 3 likes
  • 3 in conversation