topic Re: Need help for this tricky query. in SAS Programming

Need help for this tricky query.

aj34321 — Sun, 30 Dec 2012 17:34:15 GMT

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)

Re: Need help for this tricky query.

art297 — Sun, 30 Dec 2012 18:47:40 GMT

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.

Re: Need help for this tricky query.

aj34321 — Sun, 30 Dec 2012 19:03:19 GMT

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

Re: Need help for this tricky query.

art297 — Sun, 30 Dec 2012 20:46:33 GMT

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;

Re: Need help for this tricky query.

Haikuo — Mon, 31 Dec 2012 05:23:17 GMT

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

Re: Need help for this tricky query.

aj34321 — Mon, 31 Dec 2012 09:59:25 GMT

Arthur,

You are simply genious... This is exact what i wanted... Thanks a tonnnnnn...

Rgds, Anil

Re: Need help for this tricky query.

aj34321 — Sat, 05 Jan 2013 11:04:01 GMT

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

Re: Need help for this tricky query.

art297 — Sat, 05 Jan 2013 15:38:42 GMT

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);

Re: Need help for this tricky query.

aj34321 — Sat, 05 Jan 2013 19:54:58 GMT

Thanks a lot..

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

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

topic Re: Need help for this tricky query. in SAS Programming

Need help for this tricky query.

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

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