Hi... I am using the following code to compute an annual rate which seems to work and wonder if there is a better or more efficient way to achieve the same results. I am working with rates for the Fall, Winter and Spring for each year and in some instances one or more rates may be missing. I ended up writing if statements for all the possible combination that could occur. Is there a better way to do the same thing. Thanks
data Want;
format RetRate 12.2;
set Have;
if missing(Fall) and not missing(Winter) and not missing(Spring) then
RetRate = ((((1+Spring)*(1+Winter))**(1/2))-1)*100;
if not missing(Fall) and missing(Winter) and not missing(Spring) then
RetRate = ((((1+Spring)*(1+Fall))**(1/2))-1)*100;
if not missing(Fall) and not missing(Winter) and missing(Spring) then
RetRate = ((((1+Fall)*(1+Winter))**(1/2))-1)*100;
if not missing(Fall) and not missing(Winter) and not missing(Spring) then
RetRate = ((((1+Spring)*(1+Fall)*(1+Winter))**(1/3))-1)*100;
if missing(Fall) and missing(Winter) and not missing(Spring) then
RetRate = (((1+Spring)**(1/1))-1)*100;
if not missing(Fall) and missing(Winter) and missing(Spring) then
RetRate = (((1+Fall)**(1/1))-1)*100;
if missing(Fall) and not missing(Winter) and missing(Spring) then
RetRate = (((1+Winter)**(1/1))-1)*100;
run;
You can do this be realizing that you will get the same result if you (1) substitute a rate of 0 for every missing rate, but don't count them in the exponent term:
retrate=((1+coalesce(fall,0))*(1+coalesce(spring,0))*(1+coalesce(winter,0)))**(1/n(fall,spring,winter));
The coalesce function returns the leftmost non-missing value, effectively substituting a zero for a missing value.
But the N function only counts non-missing values.
You could make this slightly more structured by utilizing an array statement:
array rate {3} fall spring winter ;
retrate=((1+coalesce(rate{1},0))*(1+coalesce(rate{2},0))*(1+coalesce(rate{3},0)))**(1/n(of rate{*}));
You can do this be realizing that you will get the same result if you (1) substitute a rate of 0 for every missing rate, but don't count them in the exponent term:
retrate=((1+coalesce(fall,0))*(1+coalesce(spring,0))*(1+coalesce(winter,0)))**(1/n(fall,spring,winter));
The coalesce function returns the leftmost non-missing value, effectively substituting a zero for a missing value.
But the N function only counts non-missing values.
You could make this slightly more structured by utilizing an array statement:
array rate {3} fall spring winter ;
retrate=((1+coalesce(rate{1},0))*(1+coalesce(rate{2},0))*(1+coalesce(rate{3},0)))**(1/n(of rate{*}));
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.