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