topic Query to find the count based on the data in SAS Procedures https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444541#M69386 <P>Hi,</P><P>&nbsp;</P><P>Please help me to solve the below question -</P><P>&nbsp;</P><P>Refer to the following tables:</P><P>&nbsp;</P><P>AGENT_INFO</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P><STRONG>AGENT_CODE </STRONG></P></TD><TD><P><STRONG>&nbsp;AGENT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P></TD><TD><P><STRONG>&nbsp;WORKING_AREA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P></TD><TD><P><STRONG>&nbsp;PHONE_NO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P></TD></TR><TR><TD><P>&nbsp;A012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Lucida&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;San Jose&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;044-52981425&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A005&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Anderson&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Brisbane&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;045-21447739&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Subbarao&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Bangalore&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;077-12346674&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Mukesh&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Mumbai&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;029-12358964&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A006&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;McDen&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;London&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;078-22255588&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Ivan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Torento&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;008-22544166&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A009&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Benjamin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Hampshair&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;008-22536178&nbsp;&nbsp;&nbsp;</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>CUSTOMER_INFO</P><TABLE><TBODY><TR><TD><P><STRONG>AGENT_CODE</STRONG></P></TD><TD><P><STRONG>CUST_NAME</STRONG></P></TD><TD><P><STRONG>CUST_CITY</STRONG></P></TD><TD><P><STRONG>AMT</STRONG></P></TD><TD><P><STRONG>PHONE_NO</STRONG></P></TD><TD><P><STRONG>CUST_CODE</STRONG></P></TD></TR><TR><TD><P>A002</P></TD><TD><P>Holmes</P></TD><TD><P>London</P></TD><TD><P>4000</P></TD><TD><P>BBBBBBB</P></TD><TD><P>&nbsp;C00013</P></TD></TR><TR><TD><P>A006</P></TD><TD><P>Micheal</P></TD><TD><P>New York</P></TD><TD><P>6000</P></TD><TD><P>CCCCCCC</P></TD><TD><P>&nbsp;C00001</P></TD></TR><TR><TD><P>A004</P></TD><TD><P>Albert</P></TD><TD><P>New York</P></TD><TD><P>6000</P></TD><TD><P>BBBBSBB</P></TD><TD><P>&nbsp;C00020</P></TD></TR><TR><TD><P>A006</P></TD><TD><P>Ravindran</P></TD><TD><P>Bangalore</P></TD><TD><P>8000</P></TD><TD><P>AVAVAVA</P></TD><TD><P>&nbsp;C00025</P></TD></TR><TR><TD><P>A012</P></TD><TD><P>Cook</P></TD><TD><P>London</P></TD><TD><P>6000</P></TD><TD><P>FSDDSDF</P></TD><TD><P>&nbsp;C00024</P></TD></TR><TR><TD><P>A005</P></TD><TD><P>Stuart</P></TD><TD><P>London</P></TD><TD><P>11000</P></TD><TD><P>GFSGERS</P></TD><TD><P>&nbsp;C00015</P></TD></TR><TR><TD><P>A009</P></TD><TD><P>Bolt</P></TD><TD><P>New York</P></TD><TD><P>3000</P></TD><TD><P>DDNRDRH</P></TD><TD><P>&nbsp;C00002</P></TD></TR><TR><TD><P>A005</P></TD><TD><P>Fleming</P></TD><TD><P>Brisbane</P></TD><TD><P>5000</P></TD><TD><P>NHBGVFC</P></TD><TD><P>&nbsp;C00018</P></TD></TR><TR><TD><P>A012</P></TD><TD><P>Jacks</P></TD><TD><P>Brisbane</P></TD><TD><P>7000</P></TD><TD><P>WERTGDF</P></TD><TD><P>&nbsp;C00021</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>AGENT_COMMISON:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P><STRONG>CUST_CODE</STRONG></P></TD><TD><P><STRONG>AGENT_ COMMISSION </STRONG></P></TD></TR><TR><TD><P>&nbsp;C00013</P></TD><TD><P>0.12</P></TD></TR><TR><TD><P>&nbsp;C00001</P></TD><TD><P>0.13</P></TD></TR><TR><TD><P>&nbsp;C00020</P></TD><TD><P>0.14</P></TD></TR><TR><TD><P>&nbsp;C00025</P></TD><TD><P>0.11</P></TD></TR><TR><TD><P>&nbsp;C00024</P></TD><TD><P>0.15</P></TD></TR><TR><TD><P>&nbsp;C00015</P></TD><TD><P>0.15</P></TD></TR><TR><TD><P>&nbsp;C00002</P></TD><TD><P>0.11</P></TD></TR><TR><TD><P>&nbsp;C00018</P></TD><TD><P>0.16</P></TD></TR><TR><TD><P>&nbsp;C00021</P></TD><TD><P>0.22</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P><STRONG>Q 1</STRONG></P><P>Write a query to list down agents with no customers</P><P>&nbsp;</P><P><STRONG>Q 2</STRONG></P><P>Write a query to select the agent with most no of customers</P><P>&nbsp;</P><P><STRONG>Q 3</STRONG></P><P>List all customers who are the same working location of the Agent</P><P>&nbsp;</P><P><STRONG>Q 4</STRONG></P><P>What is the total commission amount for each agent?</P><P>&nbsp;</P><P><STRONG>Q 5</STRONG></P><P>Range the table in ascending order of the work location with highest collection</P><P>&nbsp;</P><P>&nbsp;</P><P>Thanks, in advance ! <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Sun, 11 Mar 2018 11:31:16 GMT HarshaChaube 2018-03-11T11:31:16Z Query to find the count based on the data https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444541#M69386 <P>Hi,</P><P>&nbsp;</P><P>Please help me to solve the below question -</P><P>&nbsp;</P><P>Refer to the following tables:</P><P>&nbsp;</P><P>AGENT_INFO</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P><STRONG>AGENT_CODE </STRONG></P></TD><TD><P><STRONG>&nbsp;AGENT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P></TD><TD><P><STRONG>&nbsp;WORKING_AREA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P></TD><TD><P><STRONG>&nbsp;PHONE_NO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </STRONG></P></TD></TR><TR><TD><P>&nbsp;A012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Lucida&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;San Jose&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;044-52981425&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A005&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Anderson&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Brisbane&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;045-21447739&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Subbarao&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Bangalore&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;077-12346674&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Mukesh&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Mumbai&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;029-12358964&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A006&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;McDen&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;London&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;078-22255588&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A004&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Ivan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Torento&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;008-22544166&nbsp;&nbsp;&nbsp;</P></TD></TR><TR><TD><P>&nbsp;A009&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Benjamin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;Hampshair&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</P></TD><TD><P>&nbsp;008-22536178&nbsp;&nbsp;&nbsp;</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>CUSTOMER_INFO</P><TABLE><TBODY><TR><TD><P><STRONG>AGENT_CODE</STRONG></P></TD><TD><P><STRONG>CUST_NAME</STRONG></P></TD><TD><P><STRONG>CUST_CITY</STRONG></P></TD><TD><P><STRONG>AMT</STRONG></P></TD><TD><P><STRONG>PHONE_NO</STRONG></P></TD><TD><P><STRONG>CUST_CODE</STRONG></P></TD></TR><TR><TD><P>A002</P></TD><TD><P>Holmes</P></TD><TD><P>London</P></TD><TD><P>4000</P></TD><TD><P>BBBBBBB</P></TD><TD><P>&nbsp;C00013</P></TD></TR><TR><TD><P>A006</P></TD><TD><P>Micheal</P></TD><TD><P>New York</P></TD><TD><P>6000</P></TD><TD><P>CCCCCCC</P></TD><TD><P>&nbsp;C00001</P></TD></TR><TR><TD><P>A004</P></TD><TD><P>Albert</P></TD><TD><P>New York</P></TD><TD><P>6000</P></TD><TD><P>BBBBSBB</P></TD><TD><P>&nbsp;C00020</P></TD></TR><TR><TD><P>A006</P></TD><TD><P>Ravindran</P></TD><TD><P>Bangalore</P></TD><TD><P>8000</P></TD><TD><P>AVAVAVA</P></TD><TD><P>&nbsp;C00025</P></TD></TR><TR><TD><P>A012</P></TD><TD><P>Cook</P></TD><TD><P>London</P></TD><TD><P>6000</P></TD><TD><P>FSDDSDF</P></TD><TD><P>&nbsp;C00024</P></TD></TR><TR><TD><P>A005</P></TD><TD><P>Stuart</P></TD><TD><P>London</P></TD><TD><P>11000</P></TD><TD><P>GFSGERS</P></TD><TD><P>&nbsp;C00015</P></TD></TR><TR><TD><P>A009</P></TD><TD><P>Bolt</P></TD><TD><P>New York</P></TD><TD><P>3000</P></TD><TD><P>DDNRDRH</P></TD><TD><P>&nbsp;C00002</P></TD></TR><TR><TD><P>A005</P></TD><TD><P>Fleming</P></TD><TD><P>Brisbane</P></TD><TD><P>5000</P></TD><TD><P>NHBGVFC</P></TD><TD><P>&nbsp;C00018</P></TD></TR><TR><TD><P>A012</P></TD><TD><P>Jacks</P></TD><TD><P>Brisbane</P></TD><TD><P>7000</P></TD><TD><P>WERTGDF</P></TD><TD><P>&nbsp;C00021</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>AGENT_COMMISON:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P><STRONG>CUST_CODE</STRONG></P></TD><TD><P><STRONG>AGENT_ COMMISSION </STRONG></P></TD></TR><TR><TD><P>&nbsp;C00013</P></TD><TD><P>0.12</P></TD></TR><TR><TD><P>&nbsp;C00001</P></TD><TD><P>0.13</P></TD></TR><TR><TD><P>&nbsp;C00020</P></TD><TD><P>0.14</P></TD></TR><TR><TD><P>&nbsp;C00025</P></TD><TD><P>0.11</P></TD></TR><TR><TD><P>&nbsp;C00024</P></TD><TD><P>0.15</P></TD></TR><TR><TD><P>&nbsp;C00015</P></TD><TD><P>0.15</P></TD></TR><TR><TD><P>&nbsp;C00002</P></TD><TD><P>0.11</P></TD></TR><TR><TD><P>&nbsp;C00018</P></TD><TD><P>0.16</P></TD></TR><TR><TD><P>&nbsp;C00021</P></TD><TD><P>0.22</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P><STRONG>Q 1</STRONG></P><P>Write a query to list down agents with no customers</P><P>&nbsp;</P><P><STRONG>Q 2</STRONG></P><P>Write a query to select the agent with most no of customers</P><P>&nbsp;</P><P><STRONG>Q 3</STRONG></P><P>List all customers who are the same working location of the Agent</P><P>&nbsp;</P><P><STRONG>Q 4</STRONG></P><P>What is the total commission amount for each agent?</P><P>&nbsp;</P><P><STRONG>Q 5</STRONG></P><P>Range the table in ascending order of the work location with highest collection</P><P>&nbsp;</P><P>&nbsp;</P><P>Thanks, in advance ! <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Sun, 11 Mar 2018 11:31:16 GMT https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444541#M69386 HarshaChaube 2018-03-11T11:31:16Z Re: Query to find the count based on the data https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444562#M69387 <P>Will your instructor give me credit for this homework?</P> Sun, 11 Mar 2018 16:55:06 GMT https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444562#M69387 mkeintz 2018-03-11T16:55:06Z Re: Query to find the count based on the data https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444601#M69389 <P>Give us your current answers,&nbsp;so we can comment, give you hints&nbsp;and suggest improvements.</P> Sun, 11 Mar 2018 22:05:20 GMT https://communities.sas.com/t5/SAS-Procedures/Query-to-find-the-count-based-on-the-data/m-p/444601#M69389 PGStats 2018-03-11T22:05:20Z