topic Re: how to filter the peer group companies in SAS Programming

how to filter the peer group companies

srikanthyadav44 — Sat, 12 Nov 2022 02:04:51 GMT

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

Re: how to filter the peer group companies

PGStats — Sat, 12 Nov 2022 04:50:34 GMT

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;

Re: how to filter the peer group companies

srikanthyadav44 — Mon, 14 Nov 2022 02:54:09 GMT

thanks a lot . the CODE is working.
i have to extend the code further to do further analysis.
i posting the details.
could you please help me out

thanking you in advance