topic Re: How To Sum Column by Variable Until It Hits a Limit While Skipping Certain Entries in SAS Programming

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

E_W — Thu, 03 Aug 2023 20:15:47 GMT

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.

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

ballardw — Thu, 03 Aug 2023 20:29:45 GMT

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.

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

E_W — Thu, 03 Aug 2023 20:48:03 GMT

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.

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

ballardw — Thu, 03 Aug 2023 21:10:21 GMT

@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.