topic how to find the sum of first three largest shareholdings ? in SAS Programming https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811043#M319888 <P>dear all,</P><P>i have the data of in the following format&nbsp;</P><TABLE><TBODY><TR><TD>company_name</TD><TD>year</TD><TD>percent_shares</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.07</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.77</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>2.5</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.57</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.35</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.85</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.1</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.5</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>10.76</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.37</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.01</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>9.26</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.61</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.13</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.14</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.08</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.12</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.03</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.65</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.21</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.03</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.04</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.03</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.1</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>6.81</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.25</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.58</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.18</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.18</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.05</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.36</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.39</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.07</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.64</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.07</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.33</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.17</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>18.67</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.65</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.77</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>2.5</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.57</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.35</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.85</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>10.8</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>18.94</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.14</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.12</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.08</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.12</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.21</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>6.81</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.06</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.19</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.58</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.05</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.39</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.33</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.73</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.39</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.59</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>8.12</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.57</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.75</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.07</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>3.5</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.9</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.07</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>23.13</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.2</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.93</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.48</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>2.9</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.93</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>76</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.87</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>1.2</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>5.56</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.11</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.58</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.24</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>0.34</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.74</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>3.47</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.59</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>21.92</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.04</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.65</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>3.58</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>13.94</TD></TR></TBODY></TABLE><P>it represents the company name, year and shareholding percentage.&nbsp; it the panel data of nearly 5000 companies from 2010 to 2019.&nbsp;</P><P>i have to find out the sum of first three largest shareholdings for each company for every year from 2010 to 2019.&nbsp;</P><P>my output should have the following information&nbsp;</P><TABLE><TBODY><TR><TD>company_name</TD><TD>year</TD><TD>sum of three largest shareholdigns&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>please suggest&nbsp; the appropriate SAS CODE&nbsp;</P><P>&nbsp;</P><P>thanks in advance</P> Mon, 02 May 2022 11:46:34 GMT srikanthyadav44 2022-05-02T11:46:34Z how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811043#M319888 <P>dear all,</P><P>i have the data of in the following format&nbsp;</P><TABLE><TBODY><TR><TD>company_name</TD><TD>year</TD><TD>percent_shares</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.07</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.77</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>2.5</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.57</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.35</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.85</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.1</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.5</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>10.76</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.37</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.01</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>9.26</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.61</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.13</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.14</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.08</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.12</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.03</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.65</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.21</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.03</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.04</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.03</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.1</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>6.81</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.25</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.58</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.18</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.18</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.05</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.36</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.39</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.07</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.64</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.07</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.33</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.17</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>18.67</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.65</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.77</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>2.5</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.57</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.35</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.85</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>10.8</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>18.94</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.14</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.12</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.08</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.12</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.21</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.95</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>6.81</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.06</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.19</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.58</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.05</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>1.39</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2010</TD><TD>0.33</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.73</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.39</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.59</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>8.12</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.57</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.75</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.07</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>3.5</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>1.9</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>2.07</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2010</TD><TD>23.13</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.2</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.93</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.48</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>2.9</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>1.93</TD></TR><TR><TD>3M India Ltd.</TD><TD>2010</TD><TD>76</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.87</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>1.2</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>5.56</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.11</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.58</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.24</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>0.34</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.74</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>3.47</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.59</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>21.92</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>2.04</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>4.65</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>3.58</TD></TR><TR><TD>3P Land Holdings Ltd.</TD><TD>2010</TD><TD>13.94</TD></TR></TBODY></TABLE><P>it represents the company name, year and shareholding percentage.&nbsp; it the panel data of nearly 5000 companies from 2010 to 2019.&nbsp;</P><P>i have to find out the sum of first three largest shareholdings for each company for every year from 2010 to 2019.&nbsp;</P><P>my output should have the following information&nbsp;</P><TABLE><TBODY><TR><TD>company_name</TD><TD>year</TD><TD>sum of three largest shareholdigns&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>please suggest&nbsp; the appropriate SAS CODE&nbsp;</P><P>&nbsp;</P><P>thanks in advance</P> Mon, 02 May 2022 11:46:34 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811043#M319888 srikanthyadav44 2022-05-02T11:46:34Z Re: how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811045#M319890 <P>Compute the sum for ALL companies using PROC SUMMARY, then sort so the largest are first, then take the first three each year.</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">proc summary data=have nway; class company_name year; var percent_shares; output out=_sums_ sum=; run; proc sort data=_sums_; by year descending percent_shares; run; </CODE></PRE> <P>&nbsp;</P> Mon, 02 May 2022 11:53:42 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811045#M319890 PaigeMiller 2022-05-02T11:53:42Z Re: how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811047#M319891 <P>And then I thought of a better approach</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">proc summary data=have nway; class year company_name; var percent_shares; output out=_sums_ sum=; run; proc rank data=_sums_ descending out=ranks; by year; var percent_shares; ranks percent_shares_rank; run;</CODE></PRE> <P>&nbsp;</P> <P>The output of PROC RANK, variable named PERCENT_SHARES_RANK has values 1 2 3 for the top 3 each year.</P> Mon, 02 May 2022 11:57:58 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811047#M319891 PaigeMiller 2022-05-02T11:57:58Z Re: how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811049#M319892 <P>You have a much better chance of getting an answer if you present your data as data step code, like this:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data have; length company_name $40 year 8 percent_shares 8; infile cards dsd delimiter=','; input company_name year percent_shares; cards; 20 Microns Ltd.,2010,0.07 20 Microns Ltd.,2010,0.77 20 Microns Ltd.,2010,2.5 20 Microns Ltd.,2010,0.57 20 Microns Ltd.,2010,0.35 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,1.85 20 Microns Ltd.,2010,0.1 20 Microns Ltd.,2010,0.5 20 Microns Ltd.,2010,10.76 20 Microns Ltd.,2010,0.37 20 Microns Ltd.,2010,0.01 20 Microns Ltd.,2010,9.26 20 Microns Ltd.,2010,0.61 20 Microns Ltd.,2010,0.13 20 Microns Ltd.,2010,0.14 20 Microns Ltd.,2010,0.08 20 Microns Ltd.,2010,0.12 20 Microns Ltd.,2010,0.03 20 Microns Ltd.,2010,1.65 20 Microns Ltd.,2010,0.21 20 Microns Ltd.,2010,0.03 20 Microns Ltd.,2010,0.04 20 Microns Ltd.,2010,0.03 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,0.1 20 Microns Ltd.,2010,0 20 Microns Ltd.,2010,6.81 20 Microns Ltd.,2010,1.25 20 Microns Ltd.,2010,0.58 20 Microns Ltd.,2010,0.18 20 Microns Ltd.,2010,0.18 20 Microns Ltd.,2010,1.05 20 Microns Ltd.,2010,0.36 20 Microns Ltd.,2010,1.39 20 Microns Ltd.,2010,0.07 20 Microns Ltd.,2010,0.64 20 Microns Ltd.,2010,0.07 20 Microns Ltd.,2010,0.33 20 Microns Ltd.,2010,0.17 20 Microns Ltd.,2010,18.67 20 Microns Ltd.,2010,1.65 20 Microns Ltd.,2010,0.77 20 Microns Ltd.,2010,2.5 20 Microns Ltd.,2010,0.57 20 Microns Ltd.,2010,0.35 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,1.85 20 Microns Ltd.,2010,10.8 20 Microns Ltd.,2010,18.94 20 Microns Ltd.,2010,0.14 20 Microns Ltd.,2010,0.12 20 Microns Ltd.,2010,0.08 20 Microns Ltd.,2010,0.12 20 Microns Ltd.,2010,0.21 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,6.81 20 Microns Ltd.,2010,0.06 20 Microns Ltd.,2010,1.19 20 Microns Ltd.,2010,0.58 20 Microns Ltd.,2010,1.05 20 Microns Ltd.,2010,1.39 20 Microns Ltd.,2010,0.33 3I Infotech Ltd.,2010,2.73 3I Infotech Ltd.,2010,1.39 3I Infotech Ltd.,2010,1.59 3I Infotech Ltd.,2010,8.12 3I Infotech Ltd.,2010,2.57 3I Infotech Ltd.,2010,1.75 3I Infotech Ltd.,2010,2.07 3I Infotech Ltd.,2010,3.5 3I Infotech Ltd.,2010,1.9 3I Infotech Ltd.,2010,2.07 3I Infotech Ltd.,2010,23.13 3M India Ltd.,2010,1.2 3M India Ltd.,2010,1.93 3M India Ltd.,2010,1.48 3M India Ltd.,2010,2.9 3M India Ltd.,2010,1.93 3M India Ltd.,2010,76 3P Land Holdings Ltd.,2010,4.87 3P Land Holdings Ltd.,2010,1.2 3P Land Holdings Ltd.,2010,5.56 3P Land Holdings Ltd.,2010,2.11 3P Land Holdings Ltd.,2010,4.58 3P Land Holdings Ltd.,2010,2.24 3P Land Holdings Ltd.,2010,0.34 3P Land Holdings Ltd.,2010,2.74 3P Land Holdings Ltd.,2010,3.47 3P Land Holdings Ltd.,2010,4.59 3P Land Holdings Ltd.,2010,21.92 3P Land Holdings Ltd.,2010,2.04 3P Land Holdings Ltd.,2010,4.65 3P Land Holdings Ltd.,2010,3.58 3P Land Holdings Ltd.,2010,13.94 ;run;</CODE></PRE> <P>&nbsp;</P> <P>&nbsp;</P> <P>Here is a solution:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">proc sort; by company_name year descending percent_shares; run; data want; Three_largest=0; do _N_=1 by 1 until(last.year); set have; by company_name year; if _N_&lt;=3 then Three_largest+percent_shares; end; drop percent_shares; run;</CODE></PRE> <P>&nbsp;</P> <P>&nbsp;</P> Mon, 02 May 2022 12:06:08 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811049#M319892 s_lassen 2022-05-02T12:06:08Z Re: how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811057#M319896 <P>If your data are already grouped by company name and year, then you could keep an updated array of the 3 largest percentages in a data step:&nbsp; At the end of the year calculate the sum:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data have; length company_name $40 year 8 percent_shares 8; infile cards dsd delimiter=','; input company_name year percent_shares; cards; 20 Microns Ltd.,2010,0.07 20 Microns Ltd.,2010,0.77 20 Microns Ltd.,2010,2.5 20 Microns Ltd.,2010,0.57 20 Microns Ltd.,2010,0.35 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,1.85 20 Microns Ltd.,2010,0.1 20 Microns Ltd.,2010,0.5 20 Microns Ltd.,2010,10.76 20 Microns Ltd.,2010,0.37 20 Microns Ltd.,2010,0.01 20 Microns Ltd.,2010,9.26 20 Microns Ltd.,2010,0.61 20 Microns Ltd.,2010,0.13 20 Microns Ltd.,2010,0.14 20 Microns Ltd.,2010,0.08 20 Microns Ltd.,2010,0.12 20 Microns Ltd.,2010,0.03 20 Microns Ltd.,2010,1.65 20 Microns Ltd.,2010,0.21 20 Microns Ltd.,2010,0.03 20 Microns Ltd.,2010,0.04 20 Microns Ltd.,2010,0.03 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,0.1 20 Microns Ltd.,2010,0 20 Microns Ltd.,2010,6.81 20 Microns Ltd.,2010,1.25 20 Microns Ltd.,2010,0.58 20 Microns Ltd.,2010,0.18 20 Microns Ltd.,2010,0.18 20 Microns Ltd.,2010,1.05 20 Microns Ltd.,2010,0.36 20 Microns Ltd.,2010,1.39 20 Microns Ltd.,2010,0.07 20 Microns Ltd.,2010,0.64 20 Microns Ltd.,2010,0.07 20 Microns Ltd.,2010,0.33 20 Microns Ltd.,2010,0.17 20 Microns Ltd.,2010,18.67 20 Microns Ltd.,2010,1.65 20 Microns Ltd.,2010,0.77 20 Microns Ltd.,2010,2.5 20 Microns Ltd.,2010,0.57 20 Microns Ltd.,2010,0.35 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,1.85 20 Microns Ltd.,2010,10.8 20 Microns Ltd.,2010,18.94 20 Microns Ltd.,2010,0.14 20 Microns Ltd.,2010,0.12 20 Microns Ltd.,2010,0.08 20 Microns Ltd.,2010,0.12 20 Microns Ltd.,2010,0.21 20 Microns Ltd.,2010,1.95 20 Microns Ltd.,2010,6.81 20 Microns Ltd.,2010,0.06 20 Microns Ltd.,2010,1.19 20 Microns Ltd.,2010,0.58 20 Microns Ltd.,2010,1.05 20 Microns Ltd.,2010,1.39 20 Microns Ltd.,2010,0.33 3I Infotech Ltd.,2010,2.73 3I Infotech Ltd.,2010,1.39 3I Infotech Ltd.,2010,1.59 3I Infotech Ltd.,2010,8.12 3I Infotech Ltd.,2010,2.57 3I Infotech Ltd.,2010,1.75 3I Infotech Ltd.,2010,2.07 3I Infotech Ltd.,2010,3.5 3I Infotech Ltd.,2010,1.9 3I Infotech Ltd.,2010,2.07 3I Infotech Ltd.,2010,23.13 3M India Ltd.,2010,1.2 3M India Ltd.,2010,1.93 3M India Ltd.,2010,1.48 3M India Ltd.,2010,2.9 3M India Ltd.,2010,1.93 3M India Ltd.,2010,76 3P Land Holdings Ltd.,2010,4.87 3P Land Holdings Ltd.,2010,1.2 3P Land Holdings Ltd.,2010,5.56 3P Land Holdings Ltd.,2010,2.11 3P Land Holdings Ltd.,2010,4.58 3P Land Holdings Ltd.,2010,2.24 3P Land Holdings Ltd.,2010,0.34 3P Land Holdings Ltd.,2010,2.74 3P Land Holdings Ltd.,2010,3.47 3P Land Holdings Ltd.,2010,4.59 3P Land Holdings Ltd.,2010,21.92 3P Land Holdings Ltd.,2010,2.04 3P Land Holdings Ltd.,2010,4.65 3P Land Holdings Ltd.,2010,3.58 3P Land Holdings Ltd.,2010,13.94 ;run; data want (drop=_: percent_shares); set have; by company_name year notsorted; array pct {0:3} _newpct _pct1-_pct3; retain _pct: ; if first.year then call missing(of _pct:); if percent_shares&gt;_pct1 then do; _newpct=percent_shares; call sortn(of pct{*}); end; if last.year; sum_of_3_largest=sum(of _pct:); run;</CODE></PRE> <P>&nbsp;</P> <P>&nbsp;</P> Mon, 02 May 2022 12:57:33 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811057#M319896 mkeintz 2022-05-02T12:57:33Z Re: how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811192#M319957 <P>In future posts, please post data in usable form, as shown by <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/76464">@s_lassen</a> .</P> <P>&nbsp;</P> <P>Proc Summary can do most of the task, unfortunately there seems to be no way avoiding the transpose that takes place:</P> <PRE><CODE class=" language-sas">proc summary data=have nway; class company_name year; var percent_shares; output out=top3(drop= _:) idgroup(max(percent_shares) out[3] (percent_shares)=) / autolabel autoname; run; data want; set top3; sum_top3 = sum(of percent_shares:); drop percent_shares:; run;</CODE></PRE> Tue, 03 May 2022 11:45:42 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811192#M319957 andreas_lds 2022-05-03T11:45:42Z Re: how to find the sum of first three largest shareholdings ? https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811372#M320015 thanks it is working Wed, 04 May 2022 07:06:02 GMT https://communities.sas.com/t5/SAS-Programming/how-to-find-the-sum-of-first-three-largest-shareholdings/m-p/811372#M320015 srikanthyadav44 2022-05-04T07:06:02Z