Dear All,
I'm facing a tricky query and I'm not sure how do I write code for this logic. Request you to help me in this solving this query.
This query is to compute sales agents incentives selling cards. In our incentive structure, it is proposed that the sales agent will get his incentives from 3 card. The first two cards which will be knocked off is any two cheap cards in the order of Silver, Gold, Platinum & Black.
Below is a sample dataset with input values (Agent, Silver IP, Gold IP, Platinum IP, Black IP and the sum of all input columns). Next to it is the output dataset which has 2 card units less as per the plan.
Agent A1 has sold 1 Gold, 1 Platinum and 4 black cards and the output grid will have only 4 black cards for payout. Gold & Platinum being cheaper than Black will be knocked off.
For A2, Silver and Platinum (1 card each) will be knocked off and he will be paid incentives only on 2 black cards.
For A3, 2 silver cards will be knocked off and incentives will be paid on Gold, Platinum and Black card.
For A4, no cards sold hence no incenitves
For A5, only 1 black card sold and will be knocked off hence no payout
For A6, only 2 cards sold and both will be knocked off hence no payout.
For A7, 2 cards from Silver will be knocked off and will be paid on 3 silver, 3 gold, 1 Platinum & 2 black cards.
For A9, only 2 black cards sold which will get knocked off and hence no payout.
Thanks a lot in advance...
Agent | Silver IP | Gold IP | Platinum IP | Black IP | Sum IP | Silver OP | Gold OP | Platinum OP | Black OP | Sum OP |
---|---|---|---|---|---|---|---|---|---|---|
A1 | 0 | 1 | 1 | 4 | 6 | 0 | 0 | 0 | 4 | 4 |
A2 | 1 | 0 | 1 | 2 | 4 | 0 | 0 | 0 | 2 | 2 |
A3 | 2 | 1 | 1 | 1 | 5 | 0 | 1 | 1 | 1 | 3 |
A4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
A5 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
A6 | 0 | 1 | 1 | 0 | 2 | 0 | 0 | 0 | 0 | 0 |
A7 | 5 | 3 | 1 | 2 | 11 | 3 | 3 | 1 | 2 | 9 |
A8 | 3 | 1 | 1 | 1 | 6 | 2 | 1 | 1 | 1 | 5 |
A9 | 0 | 0 | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
Regards, Anil
Mob : +971(56) 6829672
Email : anil.jagtap@gmail.com (anil dot jagtap at gmail dot com)
There is probably a more efficient way to write it, but I think that the following does what you want:
data have;
input Agent $ Silver_IP Gold_IP Platinum_IP Black_IP Sum_IP;
cards;
A1 0 1 1 4 6
A2 1 0 1 2 4
A3 2 1 1 1 5
A4 0 0 0 0 0
A5 0 0 0 1 1
A6 0 1 1 0 2
A7 5 3 1 2 11
A8 3 1 1 1 6
A9 0 0 0 2 2
;
data want (drop=_:);
set have;
set have (drop=Agent rename=(Silver_IP=Silver_OP
Gold_IP=Gold_OP
Platinum_IP=Platinum_OP
Black_IP=Black_OP
Sum_IP=Sum_OP));
_to_drop=min(2,Sum_IP);
do _i=Silver_IP to 1 by -1 while (_to_drop gt 0);
Silver_OP=Silver_OP-1;
_to_drop=_to_drop-1;
end;
do _i=Gold_IP to 1 by -1 while (_to_drop gt 0);
Gold_OP=Gold_OP-1;
_to_drop=_to_drop-1;
end;
do _i=Platinum_IP to 1 by -1 while (_to_drop gt 0);
Platinum_OP=Platinum_OP-1;
_to_drop=_to_drop-1;
end;
do _i=Black_IP to 1 by -1 while (_to_drop gt 0);
Black_OP=Black_OP-1;
_to_drop=_to_drop;
end;
Sum_OP=sum(Silver_OP,Gold_OP,Platinum_OP,Black_OP);
run;
Would you explain A8?
I would have assumed that 3 1 1 1 1 6 would result in 1 1 1 1 4 rather than the 2 1 1 1 5 as shown.
Yes you are right.. Actually its a typo mistake.
3 1 1 1 1 6 will result in 1 1 1 1 4
Rgds, Anil
There is probably a more efficient way to write it, but I think that the following does what you want:
data have;
input Agent $ Silver_IP Gold_IP Platinum_IP Black_IP Sum_IP;
cards;
A1 0 1 1 4 6
A2 1 0 1 2 4
A3 2 1 1 1 5
A4 0 0 0 0 0
A5 0 0 0 1 1
A6 0 1 1 0 2
A7 5 3 1 2 11
A8 3 1 1 1 6
A9 0 0 0 2 2
;
data want (drop=_:);
set have;
set have (drop=Agent rename=(Silver_IP=Silver_OP
Gold_IP=Gold_OP
Platinum_IP=Platinum_OP
Black_IP=Black_OP
Sum_IP=Sum_OP));
_to_drop=min(2,Sum_IP);
do _i=Silver_IP to 1 by -1 while (_to_drop gt 0);
Silver_OP=Silver_OP-1;
_to_drop=_to_drop-1;
end;
do _i=Gold_IP to 1 by -1 while (_to_drop gt 0);
Gold_OP=Gold_OP-1;
_to_drop=_to_drop-1;
end;
do _i=Platinum_IP to 1 by -1 while (_to_drop gt 0);
Platinum_OP=Platinum_OP-1;
_to_drop=_to_drop-1;
end;
do _i=Black_IP to 1 by -1 while (_to_drop gt 0);
Black_OP=Black_OP-1;
_to_drop=_to_drop;
end;
Sum_OP=sum(Silver_OP,Gold_OP,Platinum_OP,Black_OP);
run;
Arthur,
You are simply genious... This is exact what i wanted... Thanks a tonnnnnn...
Rgds, Anil
Dear Arthur,
Thanks a lot for the solution you provided, this helped me a lot in getting my project done..
Actually i need one more help if you dont mind please..
What if the the unit is replaced by volume. I mean instead of 2 cards debiting, this time i need to remove volume of say 60,000 $
Like if a person is booking below loan
Order1 - 10,000
Order2 - 5,000
Order3 - 15,000
Order4 - 50,000
So from this i need to first remove 60,000 and then incentivise on remaining amount.
So in above example, his
Order 1 will be 0
Order 2 will be 0
Order 3 will be 0 and
Order 4 will be 20,000 and the agent will earn incentives only on 20,000
in few other cases, Order 1 might be 100,000 from which 60,000 will be gone and he will get paid only on 40,000.
Would request your expert guidance this time as well...
Thanks a lot in advance...
Rgds, Anil
The line in the code that captures the total deduction is:
_to_drop=min(2,Sum_IP);
Thus, if I correctly understand the problem, that would only have to be changed to:
_to_drop=min(60000,Sum_IP);
Thanks a lot..
Stealing raw data input from Art:
data have;
input Agent $ Silver_IP Gold_IP Platinum_IP Black_IP Sum_IP;
cards;
A1 0 1 1 4 6
A2 1 0 1 2 4
A3 2 1 1 1 5
A4 0 0 0 0 0
A5 0 0 0 1 1
A6 0 1 1 0 2
A7 5 3 1 2 11
A8 3 1 1 1 6
A9 0 0 0 2 2
;
data want;
set have;
array ip silver_ip--black_ip;
array op Silver_oP Gold_oP Platinum_oP Black_oP;
_d=-2;
do _i=1 to dim(ip) until (_d>=0);
_d=ip(_i)+_d;
end;
do _j=1 to dim(op);
op(_j)=ifn(_j<_i,0,ip(_j));
end;
if _d>=0 then op(_i)=_d;
Sum_op=sum(of op(*));
drop _:;
run;
Haikuo
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.