how to divide the data into quartiles?

[srikanthyadav44]

Dear all

i have the dataset of 4200 companies. they belong to different industry groups represented by NIC. 

 i have to divide these 4200 companies into quartiles within each industry group based on their market capitaliazation (represented by mcap)

nic represents 63 different industry groups from code 10 to 96. 

my dataset format is as follows

company_name	nic	mcap
Shah Foods Ltd.	10	17.54
Progrex Ventures Ltd.	10	18.02
Madhur Industries Ltd.	10	19.35
Ledo Tea Co. Ltd.	10	37.61
Oswal Overseas Ltd.	10	39.28
Riga Sugar Co. Ltd.	10	54.16
Uniroyal Marine Exports Ltd.	10	58.7
Spectrum Foods Ltd.	10	61.91
V S F Projects Ltd.	10	64.56
Norben Tea & Exports Ltd.	10	68.15
Sampre Nutritions Ltd.	10	70.61
Thiru Arooran Sugars Ltd.	10	71.92
Gayatri Sugars Ltd.	10	81.29
Jhandewalas Foods Ltd.	10	89.27
Prima Agro Ltd.	10	93.25
Datiware Maritime Infra Ltd.	10	95
Natraj Proteins Ltd.	10	103.79
Sunil Agro Foods Ltd.	10	104.05
Valencia Nutrition Ltd.	10	106.12
Ravalgaon Sugar Farm Ltd.	10	128.04
Narmada Agrobase Ltd.	10	137.52
Parvati Sweetners & Power Ltd.	10	143.12
Tyroon Tea Co. Ltd.	10	146.97
Prima Industries Ltd.	10	164.04

i need the output in the following format 

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

please suggest me a suitable SAS code 

thanks in advance 

[Patrick · Accepted solution]

There might be better ways for doing this but below works and returns the same quartiles than what you've got in your want data.

This whitepaper might also be of interest to you: https://www.lexjansen.com/phuse/2012/pp/PP16.pdf 

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

proc means
  data=have 
/*  qntldef=5*/
  noprint
  ;
  class nic;
  var mcap;
  output out=inter(drop=_:) p25=p25 p50=p50 p75=p75;
run;

data want(drop=_:);
  if _n_=1 then 
    do;
      if 0 then set inter;
      dcl hash h1(dataset:'inter');
      h1.defineKey('nic');
      h1.defineData('p25','p50','p75');
      h1.defineDone();
    end;
  set have;
  _rc=h1.find();

  if mcap<=p25 then quartile_calc=1;
  else if mcap<=p50 then quartile_calc=2;
  else if mcap<=p75 then quartile_calc=3;
  else quartile_calc=4;

  drop p25 p50 p75;
run;

[srikanthyadav44]

thanks a lot . the code is working

[PaigeMiller]

proc rank data=have groups=4 out=want;
    by nic;
    var mcap;
    ranks mcap_quartile;
run;

By using PROC RANK with GROUPS=4, you get quartiles numbered 0 1 2 and 3. The nice thing about PROC RANK is that it has BY variables built in, it works on multiple variables at once, and it gives you several options on how to handle ties (which you may not have in this data, but is useful for other data).