Dear all
greetings of the day
i have my dataset in the following format
Company_Name | year | mcap | industry_code | MCAPRANK | ESG reporting |
Oil & Natural Gas Corpn. Ltd. | 2010 | 2,349,765 | 610 | 1 | 1 |
Oil India Ltd. | 2010 | 277,160 | 610 | 2 | 0 |
Selan Exploration Technology Ltd. | 2010 | 6,604 | 610 | 3 | 0 |
Bharat Heavy Electricals Ltd. | 2010 | 1,168,998 | 2513 | 1 | 1 |
Godrej Consumer Products Ltd. | 2011 | 118,256 | 34 | 1 | 0 |
Tata Chemicals Ltd. | 2011 | 86,662 | 34 | 2 | 0 |
Piramal Enterprises Ltd. | 2011 | 69,996 | 34 | 3 | 1 |
H M T Ltd. | 2011 | 45,032 | 34 | 4 | 0 |
K S K Energy Ventures Ltd. | 2011 | 37,636 | 34 | 5 | 0 |
B A S F India Ltd. | 2011 | 25,692 | 34 | 6 | 0 |
Peninsula Land Ltd. | 2011 | 16,550 | 34 | 7 | 0 |
Texmaco Rail & Engg. Ltd. | 2011 | 12,729 | 34 | 8 | 0 |
Alembic Ltd. | 2011 | 9,760 | 34 | 9 | 0 |
i have to identify the peer group companies. the criteria are as follows
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
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
the output should be in the following format
company_name | year | industry_code | Peer01_Low | Peer01_high | Peer02_low | Peer02_high |
Oil & Natural Gas Corpn. Ltd. | 2010 | 610 | Oil India Ltd. | 0 | Selan Exploration Technology Ltd. | 0 |
Bharat Heavy Electricals Ltd. | 2010 | 2513 | 0 | 0 | 0 | 0 |
Piramal Enterprises Ltd. | 2011 | 34 | H M T Ltd. | Tata Chemicals Ltd. | K S K Energy Ventures Ltd. | Godrej Consumer Products Ltd. |
Peer01_low = means immediately succeeding peer company based on MCAP within the industry
Peer01_high =means immediately preceding peer company based on MCAP within the industry
Peer02_low=means next to immediately succeeding peer company based on MCAP within the industry
Peer02_high =means prior to immediately preceding peer company based on MCAP within the industry
in the above table for 'Oil & Natural Gas Corpn. Ltd' peer01_high is zero, it means no peer company in the industry with a market capitalization of more than ESG Company
hope i mentioned the conditions clearly. if any clarity is required on the conditions, please let me know.
i could not genet the data lines, so, i am attaching the sample company in . CSV format.
PLEASE HELP ME OUT IN WRITING THE SAS QUERY. THANKING YOU IN ADVANCE
If variable RANK is reliable:
data have;
length name $32;
input Name & year mcap code RANK ESG;
datalines;
Oil & Natural Gas Corpn. Ltd. 2010 2349765 610 1 1
Oil India Ltd. 2010 277160 610 2 0
Selan Exploration Technology Ltd. 2010 6604 610 3 0
Bharat Heavy Electricals Ltd. 2010 1168998 2513 1 1
Godrej Consumer Products Ltd. 2011 118256 34 1 0
Tata Chemicals Ltd. 2011 86662 34 2 0
Piramal Enterprises Ltd. 2011 69996 34 3 1
H M T Ltd. 2011 45032 34 4 0
K S K Energy Ventures Ltd. 2011 37636 34 5 0
B A S F India Ltd. 2011 25692 34 6 0
Peninsula Land Ltd. 2011 16550 34 7 0
Texmaco Rail & Engg. Ltd. 2011 12729 34 8 0
Alembic Ltd. 2011 9760 34 9 0
;
proc sql;
create table want as
select
a.name, a.year, a.code,
b.name as low1, c.name as high1, d.name as low2, e.name as high2
from
have as a left join
have as b on a.code=b.code and a.year=b.year and a.rank=b.rank-1 left join
have as c on a.code=c.code and a.year=c.year and a.rank=c.rank+1 left join
have as d on a.code=d.code and a.year=d.year and a.rank=d.rank-2 left join
have as e on a.code=e.code and a.year=e.year and a.rank=e.rank+2
where a.ESG=1;
select * from want;
quit;
If variable RANK is reliable:
data have;
length name $32;
input Name & year mcap code RANK ESG;
datalines;
Oil & Natural Gas Corpn. Ltd. 2010 2349765 610 1 1
Oil India Ltd. 2010 277160 610 2 0
Selan Exploration Technology Ltd. 2010 6604 610 3 0
Bharat Heavy Electricals Ltd. 2010 1168998 2513 1 1
Godrej Consumer Products Ltd. 2011 118256 34 1 0
Tata Chemicals Ltd. 2011 86662 34 2 0
Piramal Enterprises Ltd. 2011 69996 34 3 1
H M T Ltd. 2011 45032 34 4 0
K S K Energy Ventures Ltd. 2011 37636 34 5 0
B A S F India Ltd. 2011 25692 34 6 0
Peninsula Land Ltd. 2011 16550 34 7 0
Texmaco Rail & Engg. Ltd. 2011 12729 34 8 0
Alembic Ltd. 2011 9760 34 9 0
;
proc sql;
create table want as
select
a.name, a.year, a.code,
b.name as low1, c.name as high1, d.name as low2, e.name as high2
from
have as a left join
have as b on a.code=b.code and a.year=b.year and a.rank=b.rank-1 left join
have as c on a.code=c.code and a.year=c.year and a.rank=c.rank+1 left join
have as d on a.code=d.code and a.year=d.year and a.rank=d.rank-2 left join
have as e on a.code=e.code and a.year=e.year and a.rank=e.rank+2
where a.ESG=1;
select * from want;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: