query for tabel and sumif

[sanjaymane7]

Hi,

I have data set where I want to sum of premium for particular Agent_Id and want to update incentive percentage as per their sum of premium amount.

Example -

If agent id ='14524' sum of premium = 50000 than Percentage = 10;

else if agent id='17116' sum of premium  greater 50000 and less than 100000 then 12;

else percentage=0;

run;

data set =

Agent_Id	Premium
1633655	800,000
1633655	400,000
12529	200,000
12529	100,000
17116	50,000
17116	25,000
14524	10,000
14524	10,000
14524	20,000

want result as per below

Agent_Id	Premium	Sum Of Premium	Percentage
1633655	800,000	1200000
1633655	400,000	1200000
12529	200,000	300000
12529	100,000	300000
17116	50,000	75000	12
17116	25,000	75000	12
14524	10,000	40000	10
14524	10,000	40000	10
14524	20,000	40000	10

[PaigeMiller · Accepted solution]

---

@sanjaymane7 wrote:

Hi,

I have data set where I want to sum of premium for particular Agent_Id and want to update incentive percentage as per their sum of premium amount.

Example -

If agent id ='14524' sum of premium = 50000 than Percentage = 10;

else if agent id='17116' sum of premium  greater 50000 and less than 100000 then 12;

else percentage=0;

run;

data set =

Agent_Id	Premium
1633655	800,000
1633655	400,000
12529	200,000
12529	100,000
17116	50,000
17116	25,000
14524	10,000
14524	10,000
14524	20,000

want result as per below

Agent_Id	Premium	Sum Of Premium	Percentage
1633655	800,000	1200000
1633655	400,000	1200000
12529	200,000	300000
12529	100,000	300000
17116	50,000	75000	12
17116	25,000	75000	12
14524	10,000	40000	10
14524	10,000	40000	10
14524	20,000	40000	10

---

proc summary data=have;
    class agent_id;
    var premium;
    output out=sums sum=sum_of_premium;
run;
data want;
    merge have sums;
    by agent_id;
    if sum_of_premium<50000 then percentage=10;
    else if sum_of_premium<100000 then percentage=12;
    else percentage=0;
run;
    

This assumes your data is sorted by AGENT_ID

[sanjaymane7]

Thank you. It works