creating max and min values while showing the corresponding items

[kashif mansoor]

Hi, below is a random data from my study. I want to create a variable that should contain min and max values of minwage_3 corresponding to SchOfemployment by regions (new_state). In other words, I want to create a table where min and max values of minwage_3 are shown, and also the corresponding SchOfemployment name is given, for each different region (new_state). Kindly help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str158 SchOfemployment double minwage_3 byte new_state
"Agriculture" 205 31
"Construction & maintenance of roads and buildings" 205 31
"Employment in private coaching classes/school/technical institutions" 236.54 31
"Loading and Unloading" 205 31
"Shops & commercial establishments " 256.15 31
"Stone breaking & stone crushing " 205 31
"Wood working establishments" 205 31
"Agriculture" 80 2
"Asbestos, cement and cocrete factories " 80 2
"Banks" 80 2
"Book selling and binding" 80 2
"Breweries and distilleries" 80 2
"Carpet/Shawl weaving industry" 80 2
"Casual employees/government offices contigency and works" 80 2
"Cinema/Film/Theatre industry" 80 2
"Construction & maintenance of roads and buildings" 80 2
"Cooperative credit socieities/marketing societies" 80 2
"Dal/Flour/Rice mills" 80 2
"Dispensaries" 80 2
"Employment of non-teaching staff in non-grant aided educational institutions" 80 2
"Forestry/social forestry, timbering operations " 80 2
"Hospitals, nursing homes and private clinics" 80 2
"Loading and Unloading" 80 2
"Local Authority/Municipality/Municipal Corporation/Gram Panchayat" 80 2
"Manufacturing of tobacco/cigar/beedi" 80 2
"Motor body builders/Mechanical/local transport workshops" 80 2
"Oil mills" 80 2
"Petrol & diesel oil pumps industry " 80 2
"Plantation (cincona, rubber tea & coffee etc. )" 80 2
"Plywood industry " 80 2
"Potteries industry" 80 2
"Printing presss (lithiography, photography & similar other works)" 80 2
"Private security agency " 80 2
"Public motor transport" 80 2
"Saw mills" 80 2
"Shops & commercial establishments " 80 2
"Stone breaking & stone crushing " 80 2
"Sweeper & sanitation workers" 80 2
"Agarbatti Manufacturing" 115.6 3
"Agarwood industry" 115.6 3
"Agriculture" 115.6 3
"Aluminium Industry" 115.6 3
"Asbestos, cement and cocrete factories " 115.6 3
"Bakeries and confectionaries (including biscuits) manufactory " 115.6 3
"Blacksmith" 115.6 3
"Bought leaf factory" 115.6 3
"Breweries and distilleries" 115.6 3
"Brick/ Charcoal kiln industry" 115.6 3
"Buffalo and cow milk premises" 115.6 3
"Candle and wax industry" 115.6 3
"Canteen and clubs" 115.6 3
"Cement & cement products industry" 115.6 3
"Cement and Hume pipe/cement prestressed/cement products industry" 115.6 3
"Chakki mills" 115.6 3
"Chemical/pharmaceutical industry" 115.6 3
"Cinema/Film/Theatre industry" 115.6 3
"Cleaner" 115.6 3
"Construction & maintenance of roads and buildings" 115.6 3
"Contractor's establishments of forest department" 115.6 3
"Cooperative credit socieities/marketing societies" 115.6 3
"Cotton Textile, Cotton Ginning and Pressing, Spinning, Weaving including Handloom Weaving" 115.6 3
"Dairy & dairy products/Procurement, processing, & distribution of milk" 115.6 3
"Dal/Flour/Rice mills" 115.6 3
"Dispensaries" 115.6 3
"Earth cutting/removing/filling/lebelling etc." 115.6 3
"Electricity (production & distribution)" 115.6 3
"Employment in private coaching classes/school/technical institutions" 115.6 3
"Film production/motion picture/studios (production, distribution & publicity)" 115.6 3
"Fisheries and sea food industry" 115.6 3
"Flood control " 115.6 3
"Food processing/food preservation/food products " 115.6 3
"Forestry/social forestry, timbering operations " 115.6 3
"General engineering " 115.6 3
"Gold/silver covering, gold casting & ornaments" 115.6 3
"Goldsmith" 115.6 3
"Grass cutting/wood cutting" 115.6 3
"Gutkha and pan masala " 115.6 3
"Handicraft " 115.6 3
"Handloom and weaving industry" 115.6 3
"Horticulture" 115.6 3
"Hospitals, nursing homes and private clinics" 115.6 3
"Hotels and restaurants" 115.6 3
"Hotels, restaurants & eating houses" 115.6 3
"Hydro electric projects" 115.6 3
"Ice cream, ice candy manufactory" 115.6 3
"Ice factories and cold storage " 115.6 3
"Irrigation department" 115.6 3
"Jute & coir industry" 115.6 3
"Jute bailing industry" 115.6 3
"Khadi & Village industry" 115.6 3
"Khandsari/sugar factory " 115.6 3
"L.P.G distribution" 115.6 3
"Local Authority/Municipality/Municipal Corporation/Gram Panchayat" 115.6 3
"Manufacturing of fire works/explosives " 115.6 3
"Manufacturing of papers, and paper boards, cardboards" 115.6 3
"Manufacturing of photo & picture frames " 115.6 3
"Manufacturing of soaps & detergents" 115.6 3
"Manufacturing of tobacco/cigar/beedi" 115.6 3
"Manufacturing of trunks/buckets/tin plates, and shaping & printing " 115.6 3
"Metal rolling/re-rolling (non ferrous)" 115.6 3
end
label values new_state new_sta
label def new_sta 2 "Arunachal Pradesh", modify
label def new_sta 3 "Assam", modify
label def new_sta 31 "Andaman&Nicobar", modify
[/CODE]

[Hemanshu Kumar]

You have many values that are ties. E.g. for Andaman&Nicobar, you have five observations with the same minwage_3 of 205 (which is also the minimum for the state). For Arunachal Pradesh and for Assam, you only have one value each for minwage_3 across all their respective observations.

How do you intend to deal with this?

[kashif mansoor]

Yes, you are right. There are cases where max and min are the same as those regions have uniform system. I can deal with this by matching it with the administrative records. As of now, I need to know how I an do the Stata command.

[Hemanshu Kumar]

You said

I want to create a table where min and max values of minwage_3 are shown, and also the corresponding SchOfemployment name is given

The point is, when there are multiple SchofEmployment corresponding to single minimum or maximum value of minwage_3, what do you want in the table?

[kashif mansoor]

To overcome this problem, I will restrict the data to those observations where regions have multiple minwage_3 each corresponding to a different SchOfemployment. Then, I want a table showing the min and max values of minwage_3 and the corresponding SchOfemployment.

[Hemanshu Kumar]

Okay then please work on the data and provide a better data example where this problem no longer exists, and where there is variation in maximum and minimum values.

[kashif mansoor]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str158 SchOfemployment double minwage_3 byte state_tab
"Agarbatti Manufacturing"        169.46    11
"Agriculture"        157.34    11
"Allopathic / Ayurvedic & Unani pharmacies"        161.77    11
"Areca nut"        175.81    11
"Automobile engineering/ Automobile repairing/Auto body fabrication"        163.31    11
"Bakeries and confectionaries (including biscuits) manufactory "        169.16    11
"Brass and copper industry"        154.38    11
"Breweries and distilleries"        182.12    11
"Brick/ Charcoal kiln industry"        257.2    11
"Brick/Roof tiles/Tiles"        174.91    11
"Canteen and clubs"        179.41    11
"Cashew processing industry "        190.69    11
"Cashew/coconut/cardamom gardens"        171.18    11
"Cement & cement products industry"        171.97    11
"Chemical/pharmaceutical industry"        176.53    11
"Coffee/Rubber/Cinchona plantations"        186.91    11
"Construction & maintenance of roads and buildings"        171.01    11
"Cotton ginning and processing industry"        191.39    11
"Cotton Textile, Cotton Ginning and Pressing, Spinning, Weaving including Handloom    Weaving"    186.69    11
"Dairy & dairy products/Procurement, processing, & distribution of milk"        170.7    11
"Dal/Flour/Rice mills"        168.07    11
"Domestic workers"        165.58    11
"Electronic electroplating"        174.05    11
"Electroplating/Buffing/Polishing"        181.01    11
"Employment in registered Factories not elsewhere classified"        139.26    11
"Film production/motion picture/studios (production, distribution & publicity)"        206.85    11
"Fisheries and sea food industry"        179.48    11
"Food processing/food preservation/food products "        170.86    11
"Forestry/social forestry, timbering operations "        186.01    11
"General engineering "        194.53    11
"Glass & glass ware, chinaware industry"        166.54    11
"Handloom and weaving industry"        143.51    11
"Hospitals, nursing homes and private clinics"        167.81    11
"Hostels of colleges & educational institutions "        182.44    11
"Hotels, restaurants & eating houses"        175.88    11
"Ice factories and cold storage "        177.53    11
"Iron foundary"        178.56    11
"Khandsari/sugar factory "        166.88    11
"Laundaries/ dry washing/dyeing"        171.15    11
"Mandakki bhatti"        156.38    11
"Manufacturing of fire works/explosives "        186.43    11
"Manufacturing of mosaic tiles, floor tiles & glazing tiles"        186.69    11
"Manufacturing of papers, and paper boards, cardboards"        162.28    11
"Manufacturing of spun pipe, concrete pipe, sanitary fitting, PCC, RCS poles etc."        203.28    11
"Manufacturing of tobacco/cigar/beedi"        200.23    11
"Manufacturing of tobacco/eatable tobacco"        177.06    11
"Marbles & granite industry"        157.17    11
"Medical/sales representatives"        184.84    11
"Metal rolling/re-rolling (ferrous)"        186.69    11
"Metal rolling/re-rolling (non ferrous)"        189.69    11
"Oil mills"        175.59    11
"Petrol & diesel oil pumps industry "        168.36    11
"Plantation (cincona, rubber tea & coffee etc. )"        122.89    11
"Plantation (cincona, rubber tea & coffee etc. ) non staff workers"        124.22    11
"Plastic & plastic products industry"        183.2    11
"Plywood industry "        167.82    11
"Potteries, ceramics, fire bricks industry"        178.48    11
"Powerloom industry "        143.51    11
"Printing presss (lithiography, photography & similar other works)"        179.55    11
"Private finance corporation & chit funds"        171.97    11
"Private security agency "        199.05    11
"Public motor transport"        173.88    11
"Rubber & rubber products industry"        112.99    11
"Russer products"        184.67    11
"Saw mills"        175.81    11
"Sericulture cultivation"        177.77    11
"Shops & commercial establishments "        173.69    11
"Silk textile industry"        259.15    11
"Soft drinks/Aerated water/Beverages/Juices"        174.23    11
"Spinning, weaving printing & bleaching of silk & pina fibres"        186.69    11
"Steel almirah, tables, chairs & other furniture industry"        156.38    11
"Stone breaking & stone crushing "        164.16    11
"Sweeper & sanitation workers"        165.38    11
"Tailoring/stitching/embroidery "        162.41    11
"Tanneries & manufacturing of leather goods"        156.45    11
"Timber trading (excluding felling & sawing)"        186.01    11
"Toddy tapping including selling"        175.28    11
"Veneer industry"        177.51    11
"Woolen spinning & weaving"        186.69    11
"Carpentary and masonary"        175.81    11
"Advocates or attorneys, chartered/cost accountants, auditors, tax consultants "        238.71    1
"Agriculture"        112    1
"Any Manufacturing Process as defined under section 2(k)/2(a)2(m) of the Factories    Act, 1948"    133.23    1
"Automobile engineering/ Automobile repairing/Auto body fabrication"        236.29    1
"Bakeries and confectionaries (including biscuits) manufactory "        197.28    1
"Betal vines"        217.92    1
"Breweries and distilleries"        241.5    1
"Brick/ Charcoal kiln industry"        188.96    1
"Canteen and clubs"        171.39    1
"Carpet/Shawl weaving industry"        163.23    1
"Cashew processing industry "        230.58    1
"Cashew/coconut/cardamom gardens"        125    1
"Cement & cement products industry"        172.02    1
"Cement and Hume pipe/cement prestressed/cement products industry"        204.7    1
"Chemical/pharmaceutical industry"        216.82    1
"Cinema/Film/Theatre industry"        222.35    1
"Construction & maintenance of dams/irrigation works"        213.21    1
"Construction & maintenance of roads and buildings"        208.88    1
"Cooperative credit socieities/marketing societies"        199.51    1
"Cotton carpet manufacturing"        212.5    1
end
label values state_tab tab_sta
label def tab_sta 1 "Andhra Pradesh", modify
label def tab_sta 11 "Karnataka", modify

Sir, here is the dataset. Hope this suffices.

[Hemanshu Kumar]

Consider this code:

sort state_tab
by state_tab: egen highest = rank(minwage_3), field
by state_tab: egen lowest = rank(minwage_3), track

keep if highest == 1 | lowest == 1
gen type = cond(highest == 1, "_max", "_min")
drop highest lowest
compress
rename minwage_3 wage
rename SchOfemployment occupation

reshape wide wage occupation, i(state_tab) j(type) string

which produces:

. list, noobs sep(0)
  +-------------------------------------------------------------------------------------------------------+
  |      state_tab               occupation_max   wage_max                      occupation_min   wage_min |
  |-------------------------------------------------------------------------------------------------------|
  | Andhra Pradesh   Breweries and distilleries      241.5                         Agriculture        112 |
  |      Karnataka        Silk textile industry     259.15   Rubber & rubber products industry     112.99 |
  +-------------------------------------------------------------------------------------------------------+

[kashif mansoor]

It has worked so well. Thank you very much.