Basic loop and recursion questions

Reply
Occasional Contributor
Posts: 5

Basic loop and recursion questions

Hey all, I have some questions about actually starting to use loops. I have tried working through various things and met little success.

The original data for the following is a simple table with 5 columns, an ID that represents individual bank accounts and 4 year-end balances, called BALANCE_1996 to BALANCE_1999

This little program is just a test I wrote to learn and the data is randomly generated.  It is very kludgy.  I do not have a good handle on loops and don't want to just copy-paste lines for every year or iteration in a process.

Basically the goal is to assign a value for every year a bank account had a non-zero balance and non-zero ID, then do some basic manipulations to get a sum.

Below is my code, without proper commenting ( /* */ got removed on paste I guess). The non-indented items are the comments.

  

  

     data allotment;

         set 'c:\path\totally a file.sas7bdat';

  

I make a point holder variable for each year, these are then populated below.

  

    points_1996 = 0;

    points_1997 = 0;

    points_1998 = 0;

    points_1999 = 0;

  

I define a simple interest rate going back in time (older accounts should get more), the formula is obviously 1+.06% per year before 1999.

    int_1999 = 1.00;

    int_1998 = 1.06;

    int_1997 = 1.12;

    int_1996 = 1.18;

  

Here I populate 2 or 3 'points' in every year there was a non-zero balance in the account, and also check that the ID of the account is non-zero (which means it exists).

There is a reason the later ones get a higher value, but that is not important here.

    if BALANCE_1996 > 0 and id > 0 then points_1996 = points_1996 + 2;

    if BALANCE_1997 > 0 and id > 0 then points_1997 = points_1997 + 2;

    if BALANCE_1998 > 0 and id > 0 then points_1998 = points_1998 + 3;

    if BALANCE_1999 > 0 and id > 0 then points_1999 = points_1999 + 3;

  

I make a weighted point value based on the balance in the account * the points (0, 2 or 3) * the interest rate in each year.

  

    weighted_points_1996 = BALANCE_1996 * points_1996 * int_1996;

    weighted_points_1997 = BALANCE_1997 * points_1997 * int_1997;

    weighted_points_1998 = BALANCE_1998 * points_1998 * int_1998;

    weighted_points_1999 = BALANCE_1999 * points_1999 * int_1999;

   

    run;  

At this point I've either doubled or tripled each value based on the year and then multiplied that by a simple interest rate, also based on the year.

I then want a grand total of the weighted points by year, which I call TotSum1 through TotSum4. I also want to add TotSum1 through 4 and store this value as a variable

GrandSum that I will later use as a divisor. I don't need an entire column of this grand total variable, I just need it stored to use as my denominator in a later step that

isn't shown here. The values and totals will be used to assign everything a percentage of the total.  I can get the TotSum variables with the following, but I I do not know

how to ask SAS to sum these and hold that value for later use in making a percentage rank.

  

     proc means data=allotment noprint;

         var weighted_points_1996 weighted_points_1997 weighted_points_1998 weighted_points_1999;

         output out=    summation (drop=_type_ _freq_)

         sum(weighted_points_1996-weighted_points_1999)=TotSum1-TotSum4;

     run;

It's my first post and I'm excited to get work on code that not only does its job, but does it elegantly!

Super User
Posts: 11,343

Re: Basic loop and recursion questions

Similar named variables and identical operations indicates a likely candidate for ARRAY processing, which is usually handled in some form of DO loop;

    weighted_points_1996 = BALANCE_1996 * points_1996 * int_1996;

    weighted_points_1997 = BALANCE_1997 * points_1997 * int_1997;

    weighted_points_1998 = BALANCE_1998 * points_1998 * int_1998;

    weighted_points_1999 = BALANCE_1999 * points_1999 * int_1999;

could be handled with code that for 4 sets of variables doesn't look quite as handy but you would only need to change a few characters to change processing from 1996 to 1999 to be from 1996 to 2020

in you data step replace the 4 lines above with;

array weighted  weighted_points_1996 -  weighted_points_1999;

array balance    BALANCE_1996 - BALANCE_1999;

array points       points_1996 - points_1999; /* note that there are ways to assign initial values to the variables optionally */

array int             int_1996 - int_1999;             /* but get used to the loop instructions first*/

do I = 1 to dim (weighted); /* use of DIM, which returns the number of elements defined in the array weighted means you

                                              don't have to know how many there are. This does the same operation for each matched set

                                              of variables */

     weighted = balance * points * int; /* the tells SAS which number of item in the list to process*/

end;

/* change the 1999 to 2020 and data, assuming it is there will work*/

While recursion is possible is SAS I would recommend working a long while before trying to implement it as many of the reasons for recursive code can be accomplished with other tools.

Occasional Contributor
Posts: 5

Re: Basic loop and recursion questions

Thanks for this, I will try to implement this sort of structure on the various variables and calculations and tell you how I got on over the weekend.    

Super User
Posts: 19,799

Re: Basic loop and recursion questions

I think you're looking for retain statements instead. 

By the way, that's a *very* basic interest rate calculation and should be compounded rather than simply adding 6% per year.

Perhaps start off with what you have versus where you're going?

As a learning exercise you'll get a bunch of different solutions and you can see the different ways of implementing this in SAS.

Super User
Posts: 11,343

Re: Basic loop and recursion questions

And likely later get into some reasons why it is likely better to normalize the data to a have the YEAR as a separate variable and one record per year instead of the wide format here.

Occasional Contributor
Posts: 5

Re: Basic loop and recursion questions

As for the non-compounding interest, that's perfectly fine here. There is a legal/accounting reason for me to have a non-compounded interest rate per year here. Also, if I learn how to do additive interest rate iterations, it's trivial to change that to multiplicative compounding. I'm interested in learning how to do the looping right, not the underlying finance (especially since this is just mock up).

Ask a Question
Discussion stats
  • 5 replies
  • 688 views
  • 3 likes
  • 3 in conversation