topic Re: query for tabel and sumif in SAS Programming https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606698#M176214 Thank you. It works Sat, 23 Nov 2019 18:42:22 GMT sanjaymane7 2019-11-23T18:42:22Z query for tabel and sumif https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606668#M176195 <P>Hi,</P><P>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.</P><P>&nbsp;</P><P>Example -</P><P>If agent id ='14524' sum of premium = 50000 than Percentage = 10;</P><P>else if agent id='17116' sum of premium&nbsp; greater 50000 and less than 100000 then 12;</P><P>else percentage=0;</P><P>run;</P><P>&nbsp;</P><P>data set =</P><TABLE><TBODY><TR><TD><P>Agent_Id</P></TD><TD><P>Premium</P></TD></TR><TR><TD><P>1633655</P></TD><TD><P>&nbsp;&nbsp; 800,000</P></TD></TR><TR><TD><P>1633655</P></TD><TD><P>&nbsp;&nbsp; 400,000</P></TD></TR><TR><TD><P>12529</P></TD><TD><P>&nbsp;&nbsp; 200,000</P></TD></TR><TR><TD><P>12529</P></TD><TD><P>&nbsp;&nbsp; 100,000</P></TD></TR><TR><TD><P>17116</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 50,000</P></TD></TR><TR><TD><P>17116</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 25,000</P></TD></TR><TR><TD><P>14524</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P></TD></TR><TR><TD><P>14524</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P></TD></TR><TR><TD><P>14524</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 20,000</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>want result as per below</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P>Agent_Id</P></TD><TD><P>Premium</P></TD><TD><P>Sum Of Premium</P></TD><TD><P>Percentage</P></TD></TR><TR><TD><P>1633655</P></TD><TD><P>&nbsp;&nbsp; 800,000</P></TD><TD><P>1200000</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>1633655</P></TD><TD><P>&nbsp;&nbsp; 400,000</P></TD><TD><P>1200000</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>12529</P></TD><TD><P>&nbsp;&nbsp; 200,000</P></TD><TD><P>300000</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>12529</P></TD><TD><P>&nbsp;&nbsp; 100,000</P></TD><TD><P>300000</P></TD><TD><P>&nbsp;</P></TD></TR><TR><TD><P>17116</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 50,000</P></TD><TD><P>75000</P></TD><TD><P>12</P></TD></TR><TR><TD><P>17116</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 25,000</P></TD><TD><P>75000</P></TD><TD><P>12</P></TD></TR><TR><TD><P>14524</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P></TD><TD><P>40000</P></TD><TD><P>10</P></TD></TR><TR><TD><P>14524</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P></TD><TD><P>40000</P></TD><TD><P>10</P></TD></TR><TR><TD><P>14524</P></TD><TD><P>&nbsp;&nbsp;&nbsp;&nbsp; 20,000</P></TD><TD><P>40000</P></TD><TD><P>10</P></TD></TR></TBODY></TABLE> Sat, 23 Nov 2019 10:12:05 GMT https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606668#M176195 sanjaymane7 2019-11-23T10:12:05Z Re: query for tabel and sumif https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606672#M176197 <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/296098">@sanjaymane7</a>&nbsp;wrote:<BR /> <P>Hi,</P> <P>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.</P> <P>&nbsp;</P> <P>Example -</P> <P>If agent id ='14524' sum of premium = 50000 than Percentage = 10;</P> <P>else if agent id='17116' sum of premium&nbsp; greater 50000 and less than 100000 then 12;</P> <P>else percentage=0;</P> <P>run;</P> <P>&nbsp;</P> <P>data set =</P> <TABLE> <TBODY> <TR> <TD> <P>Agent_Id</P> </TD> <TD> <P>Premium</P> </TD> </TR> <TR> <TD> <P>1633655</P> </TD> <TD> <P>&nbsp;&nbsp; 800,000</P> </TD> </TR> <TR> <TD> <P>1633655</P> </TD> <TD> <P>&nbsp;&nbsp; 400,000</P> </TD> </TR> <TR> <TD> <P>12529</P> </TD> <TD> <P>&nbsp;&nbsp; 200,000</P> </TD> </TR> <TR> <TD> <P>12529</P> </TD> <TD> <P>&nbsp;&nbsp; 100,000</P> </TD> </TR> <TR> <TD> <P>17116</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 50,000</P> </TD> </TR> <TR> <TD> <P>17116</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 25,000</P> </TD> </TR> <TR> <TD> <P>14524</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P> </TD> </TR> <TR> <TD> <P>14524</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P> </TD> </TR> <TR> <TD> <P>14524</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 20,000</P> </TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>want result as per below</P> <P>&nbsp;</P> <TABLE> <TBODY> <TR> <TD> <P>Agent_Id</P> </TD> <TD> <P>Premium</P> </TD> <TD> <P>Sum Of Premium</P> </TD> <TD> <P>Percentage</P> </TD> </TR> <TR> <TD> <P>1633655</P> </TD> <TD> <P>&nbsp;&nbsp; 800,000</P> </TD> <TD> <P>1200000</P> </TD> <TD> <P>&nbsp;</P> </TD> </TR> <TR> <TD> <P>1633655</P> </TD> <TD> <P>&nbsp;&nbsp; 400,000</P> </TD> <TD> <P>1200000</P> </TD> <TD> <P>&nbsp;</P> </TD> </TR> <TR> <TD> <P>12529</P> </TD> <TD> <P>&nbsp;&nbsp; 200,000</P> </TD> <TD> <P>300000</P> </TD> <TD> <P>&nbsp;</P> </TD> </TR> <TR> <TD> <P>12529</P> </TD> <TD> <P>&nbsp;&nbsp; 100,000</P> </TD> <TD> <P>300000</P> </TD> <TD> <P>&nbsp;</P> </TD> </TR> <TR> <TD> <P>17116</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 50,000</P> </TD> <TD> <P>75000</P> </TD> <TD> <P>12</P> </TD> </TR> <TR> <TD> <P>17116</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 25,000</P> </TD> <TD> <P>75000</P> </TD> <TD> <P>12</P> </TD> </TR> <TR> <TD> <P>14524</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P> </TD> <TD> <P>40000</P> </TD> <TD> <P>10</P> </TD> </TR> <TR> <TD> <P>14524</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 10,000</P> </TD> <TD> <P>40000</P> </TD> <TD> <P>10</P> </TD> </TR> <TR> <TD> <P>14524</P> </TD> <TD> <P>&nbsp;&nbsp;&nbsp;&nbsp; 20,000</P> </TD> <TD> <P>40000</P> </TD> <TD> <P>10</P> </TD> </TR> </TBODY> </TABLE> <HR /></BLOCKQUOTE> <PRE><CODE class=" language-sas">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&lt;50000 then percentage=10; else if sum_of_premium&lt;100000 then percentage=12; else percentage=0; run; </CODE></PRE> <P>This assumes your data is sorted by AGENT_ID</P> Sat, 23 Nov 2019 11:28:30 GMT https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606672#M176197 PaigeMiller 2019-11-23T11:28:30Z Re: query for tabel and sumif https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606698#M176214 Thank you. It works Sat, 23 Nov 2019 18:42:22 GMT https://communities.sas.com/t5/SAS-Programming/query-for-tabel-and-sumif/m-p/606698#M176214 sanjaymane7 2019-11-23T18:42:22Z