How To Sum Column by Variable Until It Hits a Limit While Skipping Certain Entries

[E_W]

Hi Everyone,

I've been able to figure out how to sum the columns by variable, but it's accommodating a the limit and jumping past certain entries that is confounding me.

Premise is we have a certain budget for each zip code in America and we have offers from each zip code ranked by internal ranking. I want to be able to skip an offer if it brings us over the limit to jump to a lower offer.

For Example with current programming:

Zip             Offer         Zip Code Limit      Cumulative Sum     Status

10002       $650        $1,000                       $650                       Accept

10002       $200        $1,000                       $850                       Accept

10002       $400        $1,000                       $1250                     Reject

10002       $100         $1,000                       $1350                     Reject

10002       $50           $1,000                       $1400                     Reject

Using the existing Retain command to get the cumulative offers, the last three offers would automatically be rejected. However, what I want to happen is for the program to skip the third offer and count and accept the last two offers that meet the standards.

[ballardw]

Let's start with discussing some not quite clearly stated terms.

" meet the standards". In terms of actual variables and rules what does this mean. I can make guesses but hate to waste time programming on a guess and then get told I guessed wrong.

For instance, why is this not likely? The "cumulative sum" is less than or equal to the Zip code limit.

Zip             Offer         Zip Code Limit      Cumulative Sum     Status
10002       $650        $1,000                       $650            Accept
10002       $200        $1,000                       $850            Accept
10002       $400        $1,000                       $1250           Reject
10002       $100        $1,000                       $950            Accept
10002       $50         $1,000                       $1000           Accept

So obviously I do not understand your criteria for what is wanted.

Show some of your actual data. I doubt if your data set currently has a value of "reject". I am not sure if you have a "cumulative sum". This is likely to be more than marginally important because I don't see any obvious "ranking" in the displayed data

Also show what the expected appearance of the output data set is.

[E_W]

Sure. The cumulative sum is what I have currently using a Retain command to sum up the offers by Zip Code. The offers themselves are arranged by quality ranking assigned to each offer for each Zip Code.

Status is something I put in to reject all entries where the cumulative sum is greater than the Zip Code limit. I can take that out for now.

Zip               Quality Ranking            Offer         Zip Code Limit      Cumulative Sum   

10002                  300                         $650        $1,000                      $650    

10002                   275                         $200        $1,000                      $850      

10002                   270                         $400       $1,000                      $1250 

10002                   255                         $100        $1,000                      $1350    

10002                   250                         $50          $1,000                      $1400

The expected appearance should be the table above along with a 6th Column stating whether the offer is Accepted or Rejected.

[ballardw · Accepted solution]

---

@E_W wrote:

Sure. The cumulative sum is what I have currently using a Retain command to sum up the offers by Zip Code. The offers themselves are arranged by quality ranking assigned to each offer for each Zip Code.

Status is something I put in to reject all entries where the cumulative sum is greater than the Zip Code limit. I can take that out for now.

Zip               Quality Ranking            Offer         Zip Code Limit      Cumulative Sum   

10002                  300                         $650        $1,000                      $650    

10002                   275                         $200        $1,000                      $850      

10002                   270                         $400       $1,000                      $1250 

10002                   255                         $100        $1,000                      $1350    

10002                   250                         $50          $1,000                      $1400

The expected appearance should be the table above along with a 6th Column stating whether the offer is Accepted or Rejected.

---

Still not clear why just because you skip the third one you can't accept the 4th. Your subject line says "skipping certain entries" you example is "stop at some rule" but no quite clearly stating the stopping rule.

It looks like two variables need to be Retained (and reset in changes in Zip) the cumulative sum and a second one that you test to see if the total would have been previously exceeded.

data want;
   set have;
   by zip;
   retain cumsum flag;
   if first.zip then do;
      cumsum=0;
      flag=0;
   end;
   cumsum+offer;
   if cumsum le ziplimit and flag=0 then status='Accept';
   else if cumsum > ziplimit then do;
      flag=1;
      status='Reject';
   end;
run;

The Flag variable is an indicator for the cumulative sum would have been exceeded and is used to not accept any further "accept" values. If this works as you need you could drop the Flag variable.

This worked for your limited data.