## re: Annualizing rates

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;``````
1 ACCEPTED SOLUTION

Accepted Solutions

## Re: re: Annualizing rates

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{*}));
``````
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
2 REPLIES 2

## Re: re: Annualizing rates

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{*}));
``````
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

## Re: re: Annualizing rates

Hi Mkeintz....thanks for your suggestions...both are awesome....Thanks
Discussion stats
• 2 replies
• 94 views
• 0 likes
• 2 in conversation