BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
twildone
Pyrite | Level 9

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
mkeintz
PROC Star

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

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

View solution in original post

2 REPLIES 2
mkeintz
PROC Star

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

--------------------------
twildone
Pyrite | Level 9
Hi Mkeintz....thanks for your suggestions...both are awesome....Thanks

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 837 views
  • 0 likes
  • 2 in conversation