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!
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.