topic from peer group of ESG companies how to find whether the ESG company has least 'synch' or not? in SAS Programming https://communities.sas.com/t5/SAS-Programming/from-peer-group-of-ESG-companies-how-to-find-whether-the-ESG/m-p/844060#M333709 <P>Dear all&nbsp;</P><P>greetings of the day</P><P>it is the continuation of the further programming for the previous data in which i requested a SAS programming code to identify the peer group companies&nbsp;</P><P>my dataset is in the following format</P><TABLE><TBODY><TR><TD>Name</TD><TD>year</TD><TD>mcap</TD><TD>synch</TD><TD>code</TD><TD>RANK</TD><TD>ESG</TD></TR><TR><TD>Oil &amp; Natural Gas Corpn. Ltd.</TD><TD>2010</TD><TD>2,349,765</TD><TD>1.60</TD><TD>610</TD><TD>1</TD><TD>1</TD></TR><TR><TD>Oil India Ltd.</TD><TD>2010</TD><TD>277,160</TD><TD>0.55</TD><TD>610</TD><TD>2</TD><TD>0</TD></TR><TR><TD>Selan Exploration Technology Ltd.</TD><TD>2010</TD><TD>6,604</TD><TD>1.53</TD><TD>610</TD><TD>3</TD><TD>0</TD></TR><TR><TD>Bharat Heavy Electricals Ltd.</TD><TD>2010</TD><TD>1,168,998</TD><TD>0.49</TD><TD>2513</TD><TD>1</TD><TD>1</TD></TR><TR><TD>Godrej Consumer Products Ltd.</TD><TD>2011</TD><TD>118,256</TD><TD>0.61</TD><TD>34</TD><TD>1</TD><TD>0</TD></TR><TR><TD>Tata Chemicals Ltd.</TD><TD>2011</TD><TD>86,662</TD><TD>0.58</TD><TD>34</TD><TD>2</TD><TD>0</TD></TR><TR><TD>Piramal Enterprises Ltd.</TD><TD>2011</TD><TD>69,996</TD><TD>0.59</TD><TD>34</TD><TD>3</TD><TD>1</TD></TR><TR><TD>H M T Ltd.</TD><TD>2011</TD><TD>45,032</TD><TD>1.35</TD><TD>34</TD><TD>4</TD><TD>0</TD></TR><TR><TD>K S K Energy Ventures Ltd.</TD><TD>2011</TD><TD>37,636</TD><TD>0.95</TD><TD>34</TD><TD>5</TD><TD>0</TD></TR><TR><TD>B A S F India Ltd.</TD><TD>2011</TD><TD>25,692</TD><TD>0.15</TD><TD>34</TD><TD>6</TD><TD>0</TD></TR><TR><TD>Peninsula Land Ltd.</TD><TD>2011</TD><TD>16,550</TD><TD>0.87</TD><TD>34</TD><TD>7</TD><TD>0</TD></TR><TR><TD>Texmaco Rail &amp; Engg. Ltd.</TD><TD>2011</TD><TD>12,729</TD><TD>2.68</TD><TD>34</TD><TD>8</TD><TD>0</TD></TR><TR><TD>Alembic Ltd.</TD><TD>2011</TD><TD>9,760</TD><TD>2.59</TD><TD>34</TD><TD>9</TD><TD>0</TD></TR><TR><TD>I T I Ltd.</TD><TD>2011</TD><TD>9,202</TD><TD>3.41</TD><TD>34</TD><TD>10</TD><TD>0</TD></TR><TR><TD>Balmer Lawrie &amp; Co. Ltd.</TD><TD>2011</TD><TD>8,876</TD><TD>2.94</TD><TD>34</TD><TD>11</TD><TD>0</TD></TR><TR><TD>Man Infraconstruction Ltd.</TD><TD>2011</TD><TD>6,957</TD><TD>3.75</TD><TD>34</TD><TD>12</TD><TD>0</TD></TR><TR><TD>D C M Shriram Ltd.</TD><TD>2011</TD><TD>6,678</TD><TD>2.77</TD><TD>34</TD><TD>13</TD><TD>0</TD></TR><TR><TD>Venky'S (India) Ltd.</TD><TD>2011</TD><TD>5,971</TD><TD>3.75</TD><TD>34</TD><TD>14</TD><TD>0</TD></TR><TR><TD>Bombay Burmah Trdg. Corpn. Ltd.</TD><TD>2011</TD><TD>5,537</TD><TD>3.25</TD><TD>34</TD><TD>15</TD><TD>0</TD></TR><TR><TD>T I L Ltd.</TD><TD>2011</TD><TD>5,058</TD><TD>3.55</TD><TD>34</TD><TD>16</TD><TD>0</TD></TR><TR><TD>Nitin Fire Protection Inds. Ltd.</TD><TD>2011</TD><TD>4,660</TD><TD>2.07</TD><TD>34</TD><TD>17</TD><TD>0</TD></TR><TR><TD>Jindal Cotex Ltd.</TD><TD>2011</TD><TD>4,559</TD><TD>3.31</TD><TD>34</TD><TD>18</TD><TD>0</TD></TR><TR><TD>Texmaco Infrastructure &amp; Holdings Ltd.</TD><TD>2011</TD><TD>4,000</TD><TD>1.65</TD><TD>34</TD><TD>19</TD><TD>0</TD></TR><TR><TD>India Glycols Ltd.</TD><TD>2011</TD><TD>3,473</TD><TD>1.12</TD><TD>34</TD><TD>20</TD><TD>0</TD></TR><TR><TD>Kokuyo Camlin Ltd.</TD><TD>2011</TD><TD>3,239</TD><TD>1.04</TD><TD>34</TD><TD>21</TD><TD>0</TD></TR><TR><TD>Globus Spirits Ltd.</TD><TD>2011</TD><TD>2,830</TD><TD>1.09</TD><TD>34</TD><TD>22</TD><TD>0</TD></TR><TR><TD>B S Ltd.</TD><TD>2011</TD><TD>1,880</TD><TD>1.60</TD><TD>34</TD><TD>23</TD><TD>0</TD></TR><TR><TD>Technocraft Industries (India) Ltd.</TD><TD>2011</TD><TD>1,758</TD><TD>0.68</TD><TD>34</TD><TD>24</TD><TD>0</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>From the above database, i have to create a dummy variable that takes the value '1' if the ESG company (e., the company with value '1' in the ESG column) has the highest&nbsp; 'synch' compared to other companies in the peer group.</P><P>&nbsp;</P><P>Peer group definition is the same as I mentioned in the previous query&nbsp;</P><P>if the company is making ESG reporting, ( i.e., value is equal to '1'), identify the companies which are closer to ESG reporting companies based on their MCAP(market capitalization) as peer companies within each industry code. the closeness should be just below the ESG company MCAP and just above the ESG company MCAP in two levels&nbsp;</P><P>in some industry groups, we cannot find peer companies just above the MCAP of ESG companies, and also in some industries, only one company. in such case no peer company</P><P>&nbsp;</P><P>&nbsp;</P><P>the output should be in the following format&nbsp;</P><TABLE><TBODY><TR><TD>name</TD><TD>year&nbsp;</TD><TD>code&nbsp;</TD><TD>dummy</TD></TR><TR><TD>Oil &amp; Natural Gas Corpn. Ltd.</TD><TD>2010</TD><TD>610</TD><TD>1</TD></TR><TR><TD>Piramal Enterprises Ltd.</TD><TD>2011</TD><TD>34</TD><TD>1</TD></TR><TR><TD>Zee Entertainment Enterprises Ltd.</TD><TD>2011</TD><TD>6020</TD><TD>0</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>hope I mentioned the conditions clearly. if any clarity is required on the conditions, please let me know.</P><P>i am attaching the .CSV file of the sample data&nbsp;</P><P>please suggest to me a SAS code to generate&nbsp; the above table</P><P>thanking you&nbsp; in advance&nbsp;</P> Mon, 14 Nov 2022 09:04:54 GMT srikanthyadav44 2022-11-14T09:04:54Z from peer group of ESG companies how to find whether the ESG company has least 'synch' or not? https://communities.sas.com/t5/SAS-Programming/from-peer-group-of-ESG-companies-how-to-find-whether-the-ESG/m-p/844060#M333709 <P>Dear all&nbsp;</P><P>greetings of the day</P><P>it is the continuation of the further programming for the previous data in which i requested a SAS programming code to identify the peer group companies&nbsp;</P><P>my dataset is in the following format</P><TABLE><TBODY><TR><TD>Name</TD><TD>year</TD><TD>mcap</TD><TD>synch</TD><TD>code</TD><TD>RANK</TD><TD>ESG</TD></TR><TR><TD>Oil &amp; Natural Gas Corpn. Ltd.</TD><TD>2010</TD><TD>2,349,765</TD><TD>1.60</TD><TD>610</TD><TD>1</TD><TD>1</TD></TR><TR><TD>Oil India Ltd.</TD><TD>2010</TD><TD>277,160</TD><TD>0.55</TD><TD>610</TD><TD>2</TD><TD>0</TD></TR><TR><TD>Selan Exploration Technology Ltd.</TD><TD>2010</TD><TD>6,604</TD><TD>1.53</TD><TD>610</TD><TD>3</TD><TD>0</TD></TR><TR><TD>Bharat Heavy Electricals Ltd.</TD><TD>2010</TD><TD>1,168,998</TD><TD>0.49</TD><TD>2513</TD><TD>1</TD><TD>1</TD></TR><TR><TD>Godrej Consumer Products Ltd.</TD><TD>2011</TD><TD>118,256</TD><TD>0.61</TD><TD>34</TD><TD>1</TD><TD>0</TD></TR><TR><TD>Tata Chemicals Ltd.</TD><TD>2011</TD><TD>86,662</TD><TD>0.58</TD><TD>34</TD><TD>2</TD><TD>0</TD></TR><TR><TD>Piramal Enterprises Ltd.</TD><TD>2011</TD><TD>69,996</TD><TD>0.59</TD><TD>34</TD><TD>3</TD><TD>1</TD></TR><TR><TD>H M T Ltd.</TD><TD>2011</TD><TD>45,032</TD><TD>1.35</TD><TD>34</TD><TD>4</TD><TD>0</TD></TR><TR><TD>K S K Energy Ventures Ltd.</TD><TD>2011</TD><TD>37,636</TD><TD>0.95</TD><TD>34</TD><TD>5</TD><TD>0</TD></TR><TR><TD>B A S F India Ltd.</TD><TD>2011</TD><TD>25,692</TD><TD>0.15</TD><TD>34</TD><TD>6</TD><TD>0</TD></TR><TR><TD>Peninsula Land Ltd.</TD><TD>2011</TD><TD>16,550</TD><TD>0.87</TD><TD>34</TD><TD>7</TD><TD>0</TD></TR><TR><TD>Texmaco Rail &amp; Engg. Ltd.</TD><TD>2011</TD><TD>12,729</TD><TD>2.68</TD><TD>34</TD><TD>8</TD><TD>0</TD></TR><TR><TD>Alembic Ltd.</TD><TD>2011</TD><TD>9,760</TD><TD>2.59</TD><TD>34</TD><TD>9</TD><TD>0</TD></TR><TR><TD>I T I Ltd.</TD><TD>2011</TD><TD>9,202</TD><TD>3.41</TD><TD>34</TD><TD>10</TD><TD>0</TD></TR><TR><TD>Balmer Lawrie &amp; Co. Ltd.</TD><TD>2011</TD><TD>8,876</TD><TD>2.94</TD><TD>34</TD><TD>11</TD><TD>0</TD></TR><TR><TD>Man Infraconstruction Ltd.</TD><TD>2011</TD><TD>6,957</TD><TD>3.75</TD><TD>34</TD><TD>12</TD><TD>0</TD></TR><TR><TD>D C M Shriram Ltd.</TD><TD>2011</TD><TD>6,678</TD><TD>2.77</TD><TD>34</TD><TD>13</TD><TD>0</TD></TR><TR><TD>Venky'S (India) Ltd.</TD><TD>2011</TD><TD>5,971</TD><TD>3.75</TD><TD>34</TD><TD>14</TD><TD>0</TD></TR><TR><TD>Bombay Burmah Trdg. Corpn. Ltd.</TD><TD>2011</TD><TD>5,537</TD><TD>3.25</TD><TD>34</TD><TD>15</TD><TD>0</TD></TR><TR><TD>T I L Ltd.</TD><TD>2011</TD><TD>5,058</TD><TD>3.55</TD><TD>34</TD><TD>16</TD><TD>0</TD></TR><TR><TD>Nitin Fire Protection Inds. Ltd.</TD><TD>2011</TD><TD>4,660</TD><TD>2.07</TD><TD>34</TD><TD>17</TD><TD>0</TD></TR><TR><TD>Jindal Cotex Ltd.</TD><TD>2011</TD><TD>4,559</TD><TD>3.31</TD><TD>34</TD><TD>18</TD><TD>0</TD></TR><TR><TD>Texmaco Infrastructure &amp; Holdings Ltd.</TD><TD>2011</TD><TD>4,000</TD><TD>1.65</TD><TD>34</TD><TD>19</TD><TD>0</TD></TR><TR><TD>India Glycols Ltd.</TD><TD>2011</TD><TD>3,473</TD><TD>1.12</TD><TD>34</TD><TD>20</TD><TD>0</TD></TR><TR><TD>Kokuyo Camlin Ltd.</TD><TD>2011</TD><TD>3,239</TD><TD>1.04</TD><TD>34</TD><TD>21</TD><TD>0</TD></TR><TR><TD>Globus Spirits Ltd.</TD><TD>2011</TD><TD>2,830</TD><TD>1.09</TD><TD>34</TD><TD>22</TD><TD>0</TD></TR><TR><TD>B S Ltd.</TD><TD>2011</TD><TD>1,880</TD><TD>1.60</TD><TD>34</TD><TD>23</TD><TD>0</TD></TR><TR><TD>Technocraft Industries (India) Ltd.</TD><TD>2011</TD><TD>1,758</TD><TD>0.68</TD><TD>34</TD><TD>24</TD><TD>0</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>From the above database, i have to create a dummy variable that takes the value '1' if the ESG company (e., the company with value '1' in the ESG column) has the highest&nbsp; 'synch' compared to other companies in the peer group.</P><P>&nbsp;</P><P>Peer group definition is the same as I mentioned in the previous query&nbsp;</P><P>if the company is making ESG reporting, ( i.e., value is equal to '1'), identify the companies which are closer to ESG reporting companies based on their MCAP(market capitalization) as peer companies within each industry code. the closeness should be just below the ESG company MCAP and just above the ESG company MCAP in two levels&nbsp;</P><P>in some industry groups, we cannot find peer companies just above the MCAP of ESG companies, and also in some industries, only one company. in such case no peer company</P><P>&nbsp;</P><P>&nbsp;</P><P>the output should be in the following format&nbsp;</P><TABLE><TBODY><TR><TD>name</TD><TD>year&nbsp;</TD><TD>code&nbsp;</TD><TD>dummy</TD></TR><TR><TD>Oil &amp; Natural Gas Corpn. Ltd.</TD><TD>2010</TD><TD>610</TD><TD>1</TD></TR><TR><TD>Piramal Enterprises Ltd.</TD><TD>2011</TD><TD>34</TD><TD>1</TD></TR><TR><TD>Zee Entertainment Enterprises Ltd.</TD><TD>2011</TD><TD>6020</TD><TD>0</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>hope I mentioned the conditions clearly. if any clarity is required on the conditions, please let me know.</P><P>i am attaching the .CSV file of the sample data&nbsp;</P><P>please suggest to me a SAS code to generate&nbsp; the above table</P><P>thanking you&nbsp; in advance&nbsp;</P> Mon, 14 Nov 2022 09:04:54 GMT https://communities.sas.com/t5/SAS-Programming/from-peer-group-of-ESG-companies-how-to-find-whether-the-ESG/m-p/844060#M333709 srikanthyadav44 2022-11-14T09:04:54Z