re: Annualizing rates

[twildone]

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;

[mkeintz · Accepted solution]

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{*}));

[twildone]

Hi Mkeintz....thanks for your suggestions...both are awesome....Thanks