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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Calcite | Level 5 E_W
Calcite | Level 5

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
Super User

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 156 views
  • 1 like
  • 2 in conversation