how to Identify Peer group companies from the quartiles?

[srikanthyadav44]

dear all 

i have the data set of 4200 companies having details of their industry classification(NIC ), the quartile to which the company belongs( computed based their market capitalization) and whether the company belongs to ESG group or not. 

with this data set, i have to prepare a new dataset, which shows the peer group companies for each ESG companies. Peer group companies should be selected based the quartile group within each NIC group.

For example Jubilant Foodworks Ltd.  is an ESG company  in the NIC group of 10 and it belongs to quartile 4 in this NIC group. So, all the remaining NON-ESG companies in the same quartile of NIC-10 should be considered as  peer companies.  the same procedure should be followed for identifying the peer group companies for remaining ESG companies. 

i have the following dataset 

company_name	NIC	quartile_group	ESG_company	mcap
20 Microns Ltd.	8	1	NON_ESG	1241.2
Raghav Productivity Enhancers Ltd.	8	2	NON_ESG	2474.36
Ashapura Minechem Ltd.	8	3	NON_ESG	9077
Shah Foods Ltd.	10	1	NON_ESG	17.54
Progrex Ventures Ltd.	10	1	NON_ESG	18.02
Madhur Industries Ltd.	10	1	NON_ESG	19.35
Ledo Tea Co. Ltd.	10	1	NON_ESG	37.61
Oswal Overseas Ltd.	10	1	NON_ESG	39.28
Riga Sugar Co. Ltd.	10	1	NON_ESG	54.16
Uniroyal Marine Exports Ltd.	10	1	NON_ESG	58.7
Spectrum Foods Ltd.	10	1	NON_ESG	61.91
V S F Projects Ltd.	10	1	NON_ESG	64.56
Norben Tea & Exports Ltd.	10	1	NON_ESG	68.15
Sampre Nutritions Ltd.	10	1	NON_ESG	70.61
Thiru Arooran Sugars Ltd.	10	1	NON_ESG	71.92
Gayatri Sugars Ltd.	10	1	NON_ESG	81.29
Jhandewalas Foods Ltd.	10	1	NON_ESG	89.27
Prima Agro Ltd.	10	1	NON_ESG	93.25
Datiware Maritime Infra Ltd.	10	1	NON_ESG	95
Natraj Proteins Ltd.	10	1	NON_ESG	103.79
Sunil Agro Foods Ltd.	10	1	NON_ESG	104.05
Valencia Nutrition Ltd.	10	1	NON_ESG	106.12
Ravalgaon Sugar Farm Ltd.	10	1	NON_ESG	128.04
Narmada Agrobase Ltd.	10	1	NON_ESG	137.52
Parvati Sweetners & Power Ltd.	10	1	NON_ESG	143.12
Tyroon Tea Co. Ltd.	10	1	NON_ESG	146.97
Prima Industries Ltd.	10	1	NON_ESG	164.04
Saboo Sodium Chloro Ltd.	10	1	NON_ESG	185
Sir Shadi Lal Enterprises Ltd.	10	1	NON_ESG	186.64
Diligent Industries Ltd.	10	1	NON_ESG	187.55
Lotus Chocolate Co. Ltd.	10	1	NON_ESG	188.76
Poona Dal & Oil Inds. Ltd.	10	1	NON_ESG	189.22
Dharani Sugars & Chemicals Ltd.	10	1	NON_ESG	215.14
Kanco Tea & Inds. Ltd.	10	1	NON_ESG	240.77
Tirupati Starch & Chemicals Ltd.	10	1	NON_ESG	241.53
Terai Tea Co. Ltd.	10	1	NON_ESG	242.5
Diana Tea Co. Ltd.	10	1	NON_ESG	243.6
Kohinoor Foods Ltd.	10	1	NON_ESG	250.05
Kovilpatti Lakshmi Roller Flour Mills Ltd.	10	2	NON_ESG	254.35
Shanti Overseas (India) Ltd.	10	2	NON_ESG	260.99
Megastar Foods Ltd.	10	2	NON_ESG	267.4
Universal Starch-Chem Allied Ltd.	10	2	NON_ESG	285.6
S B E C Sugar Ltd.	10	2	NON_ESG	285.92
Simbhaoli Sugars Ltd.	10	2	NON_ESG	299.48
Ajooni Biotech Ltd.	10	2	NON_ESG	304.17
M K Proteins Ltd.	10	2	NON_ESG	331.58
Ovobel Foods Ltd.	10	2	NON_ESG	342.33
Bengal Tea & Fabrics Ltd.	10	2	NON_ESG	348.53
Indian Sucrose Ltd.	10	2	NON_ESG	350.21
Chordia Food Products Ltd.	10	2	NON_ESG	402.83
Kesar Enterprises Ltd.	10	2	NON_ESG	403.19
Virat Crane Inds. Ltd.	10	2	NON_ESG	436.05
Joonktollee Tea & Inds. Ltd.	10	2	NON_ESG	448.19
B & A Ltd.	10	2	NON_ESG	459.42
Rajshree Sugars & Chemicals Ltd.	10	2	NON_ESG	488.71
Gayatri Bioorganics Ltd.	10	2	NON_ESG	507.4
Kwality Ltd.	10	2	NON_ESG	523.74
Warren Tea Ltd.	10	2	NON_ESG	545.55
Kings Infra Ventures Ltd.	10	2	NON_ESG	583.1
James Warren Tea Ltd.	10	2	NON_ESG	671.07
Peria Karamalai Tea & Produce Co. Ltd.	10	2	NON_ESG	680.78
Manpasand Beverages Ltd.	10	2	NON_ESG	686.2
Sharat Industries Ltd.	10	2	NON_ESG	716.51
Kothari Fermentation & Biochem Ltd.	10	2	NON_ESG	769.5
Davangere Sugar Co. Ltd.	10	2	NON_ESG	818.56
Gillanders Arbuthnot & Co. Ltd.	10	2	NON_ESG	903.31
Modi Naturals Ltd.	10	2	NON_ESG	923.92
Grob Tea Co. Ltd.	10	2	NON_ESG	933.76
Neelamalai Agro Inds. Ltd.	10	2	NON_ESG	982.86
Sayaji Industries Ltd.	10	2	NON_ESG	984.02
Piccadily Agro Inds. Ltd.	10	2	NON_ESG	1033.96
K M Sugar Mills Ltd.	10	2	NON_ESG	1072.72
Ajanta Soya Ltd.	10	2	NON_ESG	1142.86
Mangalam Global Enterprise Ltd.	10	3	NON_ESG	1148.94
Anjani Foods Ltd.	10	3	NON_ESG	1226.96
S K M Egg Products Export (India) Ltd.	10	3	NON_ESG	1232.9
Ponni Sugars (Erode) Ltd.	10	3	NON_ESG	1269.99
Umang Dairies Ltd.	10	3	NON_ESG	1303.69
Dangee Dums Ltd.	10	3	NON_ESG	1328.8
Raj Oil Mills Ltd.	10	3	NON_ESG	1339.99
Bambino Agro Inds. Ltd.	10	3	NON_ESG	1360.7
Mawana Sugars Ltd.	10	3	NON_ESG	1405.27
Magadh Sugar & Energy Ltd.	10	3	NON_ESG	1431.71
Rana Sugars Ltd.	10	3	NON_ESG	1478.09
United Nilgiri Tea Estates Co. Ltd.	10	3	NON_ESG	1489.48
Milkfood Ltd.	10	3	NON_ESG	1587.6
K C P Sugar & Inds. Corpn. Ltd.	10	3	NON_ESG	1669.59
Sheetal Cool Products Ltd.	10	3	NON_ESG	1726.2
Dhunseri Tea & Inds. Ltd.	10	3	NON_ESG	1804.3
Coastal Corporation Ltd.	10	3	NON_ESG	1826.07
Jay Shree Tea & Inds. Ltd.	10	3	NON_ESG	1912.41
Euro India Fresh Foods Ltd.	10	3	NON_ESG	1915.8
Ugar Sugar Works Ltd.	10	3	NON_ESG	1954.69
Kriti Nutrients Ltd.	10	3	NON_ESG	1984.1
Mcleod Russel India Ltd.	10	3	NON_ESG	1995.1
Amrit Corp. Ltd.	10	3	NON_ESG	2096.38
Kothari Sugars & Chemicals Ltd.	10	3	NON_ESG	2275.29
B C L Industries Ltd.	10	3	NON_ESG	2580.43
Gokul Agro Resources Ltd.	10	3	NON_ESG	2667.58
Foods & Inns Ltd.	10	3	NON_ESG	2738.4
Vijay Solvex Ltd.	10	3	NON_ESG	2882.1
D C M Shriram Inds. Ltd.	10	3	NON_ESG	3269.17
Uttam Sugar Mills Ltd.	10	3	NON_ESG	3466.76
Avadh Sugar & Energy Ltd.	10	3	NON_ESG	3704.41
Jayant Agro-Organics Ltd.	10	3	NON_ESG	4084.5
Waterbase Ltd.	10	3	NON_ESG	4154.07
Vishwaraj Sugar Inds. Ltd.	10	3	NON_ESG	4478.55
Goodricke Group Ltd.	10	3	NON_ESG	4511.16
Dwarikesh Sugar Inds. Ltd.	10	4	NON_ESG	5799.69
Vadilal Industries Ltd.	10	4	NON_ESG	6448.74
Apex Frozen Foods Ltd.	10	4	NON_ESG	6481.25
Bajaj Hindusthan Sugar Ltd.	10	4	NON_ESG	7022.4
K S E Ltd.	10	4	NON_ESG	7359.84
Parag Milk Foods Ltd.	10	4	NON_ESG	8878.29
Manorama Industries Ltd.	10	4	NON_ESG	9002.48
Andrew Yule & Co. Ltd.	10	4	NON_ESG	9656.78
Dhampur Sugar Mills Ltd.	10	4	NON_ESG	12138.97
Prataap Snacks Ltd.	10	4	NON_ESG	13697.16
Dalmia Bharat Sugar & Inds. Ltd.	10	4	NON_ESG	15105.3
A D F Foods Ltd.	10	4	NON_ESG	17874.78
D F M Foods Ltd.	10	4	NON_ESG	18345.1
Shree Renuka Sugars Ltd.	10	4	NON_ESG	19507.61
Mrs. Bectors Food Specialities Ltd.	10	4	NON_ESG	19759.39
Bannari Amman Sugars Ltd.	10	4	NON_ESG	19777.3
Agro Tech Foods Ltd.	10	4	NON_ESG	20365.39
Triveni Engineering & Inds. Ltd.	10	4	NON_ESG	20434.35
Venky'S (India) Ltd.	10	4	NON_ESG	21907.22
Tata Coffee Ltd.	10	4	NON_ESG	22379.76
Gujarat Ambuja Exports Ltd.	10	4	NON_ESG	29337.72
C C L Products (India) Ltd.	10	4	NON_ESG	31135.18
Tasty Bite Eatables Ltd.	10	4	NON_ESG	36777.45
Hindustan Foods Ltd.	10	4	NON_ESG	44594.4
Balrampur Chini Mills Ltd.	10	4	NON_ESG	45045
E I D-Parry (India) Ltd.	10	4	NON_ESG	56411.54
Avanti Feeds Ltd.	10	4	NON_ESG	56490.84
Godrej Agrovet Ltd.	10	4	NON_ESG	100669.68
Zydus Wellness Ltd.	10	4	NON_ESG	125493.72
Ruchi Soya Inds. Ltd.	10	4	NON_ESG	189833.78
Jubilant Foodworks Ltd.	10	4	ESG	384293.84
Marico Ltd.	10	4	ESG	531261.39
Tata Consumer Products Ltd.	10	4	ESG	588595.08
Britannia Industries Ltd.	10	4	ESG	873526.94

i need the output in the following format 

ESG_company	Peer_companies
Jubilant Foodworks Ltd.	Dwarikesh Sugar Inds. Ltd.
	Vadilal Industries Ltd.
	Apex Frozen Foods Ltd.
	Bajaj Hindusthan Sugar Ltd.
	K S E Ltd.
	Parag Milk Foods Ltd.
	Manorama Industries Ltd.
	Andrew Yule & Co. Ltd.
	Dhampur Sugar Mills Ltd.
	Prataap Snacks Ltd.
	Dalmia Bharat Sugar & Inds. Ltd.
	A D F Foods Ltd.
	D F M Foods Ltd.
	Shree Renuka Sugars Ltd.
	Mrs. Bectors Food Specialities Ltd.
	Bannari Amman Sugars Ltd.
	Agro Tech Foods Ltd.
	Triveni Engineering & Inds. Ltd.
	Venky'S (India) Ltd.
	Tata Coffee Ltd.
	Gujarat Ambuja Exports Ltd.
	C C L Products (India) Ltd.
	Tasty Bite Eatables Ltd.
	Hindustan Foods Ltd.
	Balrampur Chini Mills Ltd.
	E I D-Parry (India) Ltd.
	Avanti Feeds Ltd.
	Godrej Agrovet Ltd.
	Zydus Wellness Ltd.
	Ruchi Soya Inds. Ltd.
Marico Ltd.	Dwarikesh Sugar Inds. Ltd.
	Vadilal Industries Ltd.
	Apex Frozen Foods Ltd.
	Bajaj Hindusthan Sugar Ltd.
	K S E Ltd.
	Parag Milk Foods Ltd.
	Manorama Industries Ltd.
	Andrew Yule & Co. Ltd.
	Dhampur Sugar Mills Ltd.
	Prataap Snacks Ltd.
	Dalmia Bharat Sugar & Inds. Ltd.
	A D F Foods Ltd.
	D F M Foods Ltd.
	Shree Renuka Sugars Ltd.
	Mrs. Bectors Food Specialities Ltd.
	Bannari Amman Sugars Ltd.
	Agro Tech Foods Ltd.
	Triveni Engineering & Inds. Ltd.
	Venky'S (India) Ltd.
	Tata Coffee Ltd.
	Gujarat Ambuja Exports Ltd.
	C C L Products (India) Ltd.
	Tasty Bite Eatables Ltd.
	Hindustan Foods Ltd.
	Balrampur Chini Mills Ltd.
	E I D-Parry (India) Ltd.
	Avanti Feeds Ltd.
	Godrej Agrovet Ltd.
	Zydus Wellness Ltd.
	Ruchi Soya Inds. Ltd.

please suggest me a suitable SAS code 

thanks in advance 

[PaigeMiller]

Please provide your data in SAS data step code form, as was done by @Patrick in your other thread. Here are instructions for providing the data in the proper form. No other format is acceptable.

[srikanthyadav44]

i unable to create a datastep with dataline.

i am attaching the sample of my data in .csv format 

please help me writing the SAS CODE 

[PaigeMiller]

Since I refuse to download files (any files), I can't help you unless the data is the requested form.

If you are having trouble getting the data in the requested form, you can ask questions.

[Reeza]

proc sort data=have;
by NIC quartile_group;
run;

proc sql;
create table want as
select a.company_name, b.company_name as matching_company
from have as a
left join have as b
on a.NIC=b.NIC 
and a.quartile_group=b.quartile_group 
and a.company_name ne b.company_name /*do not match with same company*/
and a.ESG_company=b.ESG_company;
quit;

[srikanthyadav44]

i tried with this code. but i am not getting the output with the desired features.
it means the code is not identifying the ESG companies and not matching the peer companies as non-ESG companies from the same quartile of the ESG companies

[Reeza]

a.ESG_company=b.ESG_company

If that line is included it should only match on the same ESG_COMPANIES.

Show an example of the expected output versus the output generated for one or two companies.

[srikanthyadav44]

i am getting the output in the this format

company_name	matching_company
20 Microns Ltd.
Raghav Productivity Enhancers Ltd.
Ashapura Minechem Ltd.
Gayatri Sugars Ltd.	Madhur Industries Ltd.
Diana Tea Co. Ltd.	Madhur Industries Ltd.
Narmada Agrobase Ltd.	Madhur Industries Ltd.
Ledo Tea Co. Ltd.	Madhur Industries Ltd.
Uniroyal Marine Exports Ltd.	Madhur Industries Ltd.
Sampre Nutritions Ltd.	Madhur Industries Ltd.
Datiware Maritime Infra Ltd.	Madhur Industries Ltd.
Oswal Overseas Ltd.	Madhur Industries Ltd.
Prima Agro Ltd.	Madhur Industries Ltd.
Natraj Proteins Ltd.	Madhur Industries Ltd.
Norben Tea & Exports Ltd.	Madhur Industries Ltd.
Poona Dal & Oil Inds. Ltd.	Madhur Industries Ltd.
Riga Sugar Co. Ltd.	Madhur Industries Ltd.
Valencia Nutrition Ltd.	Madhur Industries Ltd.
Sunil Agro Foods Ltd.	Madhur Industries Ltd.
Kohinoor Foods Ltd.	Madhur Industries Ltd.
Tyroon Tea Co. Ltd.	Madhur Industries Ltd.
Terai Tea Co. Ltd.	Madhur Industries Ltd.
Shah Foods Ltd.	Madhur Industries Ltd.
Sir Shadi Lal Enterprises Ltd.	Madhur Industries Ltd.
Saboo Sodium Chloro Ltd.	Madhur Industries Ltd.
Lotus Chocolate Co. Ltd.	Madhur Industries Ltd.
V S F Projects Ltd.	Madhur Industries Ltd.
Spectrum Foods Ltd.	Madhur Industries Ltd.
Progrex Ventures Ltd.	Madhur Industries Ltd.
Jhandewalas Foods Ltd.	Madhur Industries Ltd.
Dharani Sugars & Chemicals Ltd.	Madhur Industries Ltd.
Thiru Arooran Sugars Ltd.	Madhur Industries Ltd.
Diligent Industries Ltd.	Madhur Industries Ltd.
Prima Industries Ltd.	Madhur Industries Ltd.
Ravalgaon Sugar Farm Ltd.	Madhur Industries Ltd.
Kanco Tea & Inds. Ltd.	Madhur Industries Ltd.
Tirupati Starch & Chemicals Ltd.	Madhur Industries Ltd.
Parvati Sweetners & Power Ltd.	Madhur Industries Ltd.
Madhur Industries Ltd.	Gayatri Sugars Ltd.
Diana Tea Co. Ltd.	Gayatri Sugars Ltd.
Narmada Agrobase Ltd.	Gayatri Sugars Ltd.
Ledo Tea Co. Ltd.	Gayatri Sugars Ltd.
Uniroyal Marine Exports Ltd.	Gayatri Sugars Ltd.
Sampre Nutritions Ltd.	Gayatri Sugars Ltd.
Datiware Maritime Infra Ltd.	Gayatri Sugars Ltd.
Oswal Overseas Ltd.	Gayatri Sugars Ltd.
Prima Agro Ltd.	Gayatri Sugars Ltd.
Natraj Proteins Ltd.	Gayatri Sugars Ltd.
Norben Tea & Exports Ltd.	Gayatri Sugars Ltd.
Poona Dal & Oil Inds. Ltd.	Gayatri Sugars Ltd.
Riga Sugar Co. Ltd.	Gayatri Sugars Ltd.
Valencia Nutrition Ltd.	Gayatri Sugars Ltd.
Sunil Agro Foods Ltd.	Gayatri Sugars Ltd.
Kohinoor Foods Ltd.	Gayatri Sugars Ltd.
Tyroon Tea Co. Ltd.	Gayatri Sugars Ltd.
Terai Tea Co. Ltd.	Gayatri Sugars Ltd.
Shah Foods Ltd.	Gayatri Sugars Ltd.

here companies in both the columns are not ESG companies 

[Reeza]

Show me an example of what the answer should be then. Make a smaller example with say 5 to 10 companies and a mix of ESG and NON-ESG.

From your description I read matching as 'peer' companies, companies within the same classification. This seems like a non-peer match to match with non-esg companies. Since it seems hard to explain a small data example would clarify the issue.

Show what you have as a starting data, what you expect as output EXACTLY from that input data.

[srikanthyadav44]

Dear Reeza 

an example of my required output 

i need the output in the following format 

ESG_company	Peer_companies
Jubilant Foodworks Ltd.	Dwarikesh Sugar Inds. Ltd.
	Vadilal Industries Ltd.
	Apex Frozen Foods Ltd.
	Bajaj Hindusthan Sugar Ltd.
	K S E Ltd.
	Parag Milk Foods Ltd.
	Manorama Industries Ltd.
	Andrew Yule & Co. Ltd.
	Dhampur Sugar Mills Ltd.
	Prataap Snacks Ltd.
	Dalmia Bharat Sugar & Inds. Ltd.
	A D F Foods Ltd.
	D F M Foods Ltd.
	Shree Renuka Sugars Ltd.
	Mrs. Bectors Food Specialities Ltd.
	Bannari Amman Sugars Ltd.
	Agro Tech Foods Ltd.
	Triveni Engineering & Inds. Ltd.
	Venky'S (India) Ltd.
	Tata Coffee Ltd.
	Gujarat Ambuja Exports Ltd.
	C C L Products (India) Ltd.
	Tasty Bite Eatables Ltd.
	Hindustan Foods Ltd.
	Balrampur Chini Mills Ltd.
	E I D-Parry (India) Ltd.
	Avanti Feeds Ltd.
	Godrej Agrovet Ltd.
	Zydus Wellness Ltd.
	Ruchi Soya Inds. Ltd.
Marico Ltd.	Dwarikesh Sugar Inds. Ltd.
	Vadilal Industries Ltd.
	Apex Frozen Foods Ltd.
	Bajaj Hindusthan Sugar Ltd.
	K S E Ltd.
	Parag Milk Foods Ltd.
	Manorama Industries Ltd.
	Andrew Yule & Co. Ltd.
	Dhampur Sugar Mills Ltd.
	Prataap Snacks Ltd.
	Dalmia Bharat Sugar & Inds. Ltd.
	A D F Foods Ltd.
	D F M Foods Ltd.
	Shree Renuka Sugars Ltd.
	Mrs. Bectors Food Specialities Ltd.
	Bannari Amman Sugars Ltd.
	Agro Tech Foods Ltd.
	Triveni Engineering & Inds. Ltd.
	Venky'S (India) Ltd.
	Tata Coffee Ltd.
	Gujarat Ambuja Exports Ltd.
	C C L Products (India) Ltd.
	Tasty Bite Eatables Ltd.
	Hindustan Foods Ltd.
	Balrampur Chini Mills Ltd.
	E I D-Parry (India) Ltd.
	Avanti Feeds Ltd.
	Godrej Agrovet Ltd.
	Zydus Wellness Ltd.
	Ruchi Soya Inds. Ltd.

in the above output Jubilant Foodworks Ltd. and  Marico Ltd  are ESG companies belonging to NIC group 10 and quantile 4. So, all the remaining companies in the same quantile will become peer group companies to those companies. 

i hope you understood my requirement now

thanks in advance  

[Reeza]

Seems like you want to match ESG with NON ESG peers based on quartiles?

So in that case simply change the condition to be not equal. 

Or specify that data from table A should be ESG and data from table B should ben NON-ESG. 

Play around with the equalities if this doesn't work but you should be able to figure it out from here. 

proc sort data=have;
by NIC quartile_group;
run;

proc sql;
create table want as
select a.company_name, b.company_name as matching_company
from have as a
left join have as b
on a.NIC=b.NIC 
and a.quartile_group=b.quartile_group 
and a.company_name ne b.company_name /*do not match with same company*/
/*may want this to filter only ESG as main companies*/
and a.ESG_company = 'ESG'
and a.ESG_company != b.ESG_company;
quit;

[Kurt_Bremser]

This is an example for your data in a data step:

data have;
infile datalines dlm="|" dsd truncover;
input company_name :$50. NIC quartile_group ESG_company :$7. mcap;
datalines;
20 Microns Ltd.|8|1|NON_ESG|1241.2
Raghav Productivity Enhancers Ltd.|8|2|NON_ESG|2474.36
;

Note that I used the pipe character as delimiter because of the company names containing blanks.

From here it is child's play to add the rest of your data. Unless you are simply too lazy to do it, of course, in which case we will be too lazy to help you.