---------------------------------------------------------------------------------------------------------------------------- log: c:\paulafiles\teach\harris\practicum_f09\emp_spec_prog10.txt log type: text opened on: 21 Oct 2009, 12:03:22 . ************************************************************************************** ; . * read in file with list of desired industries (sectors) and save as STATA dataset * ; . * ...ultimately will merge this file to select only the industries we want * ; . * ...list of desired industries is based on published figures for La Plata * ; . * county, Colorado * ; . ************************************************************************************** ; . insheet using "c:\paulafiles\teach\harris\practicum_f09\industry_list_01.csv", names; (4 vars, 20 obs) . sort industry; . save tempdata, replace; file tempdata.dta saved . clear; . *********************************************************************** ; . * read in file of IL employment by industry & county and look at it * ; . *********************************************************************** ; . insheet using "c:\paulafiles\teach\harris\practicum_f09\table_p49_census_2000_illinois_rev03.csv" , names; (104 vars, 54 obs) . describe, fullnames; Contains data obs: 54 vars: 104 size: 17,712 (99.8% of memory free) ------------------------------------------------------------------------------- storage display value variable name type format label variable label ------------------------------------------------------------------------------- industry str84 %84s gender str6 %9s adamscountyillinois int %8.0g Adams County, Illinois alexandercountyillinois int %8.0g Alexander County, Illinois bondcountyillinois int %8.0g Bond County, Illinois boonecountyillinois int %8.0g Boone County, Illinois browncountyillinois int %8.0g Brown County, Illinois bureaucountyillinois int %8.0g Bureau County, Illinois calhouncountyillinois int %8.0g Calhoun County, Illinois carrollcountyillinois int %8.0g Carroll County, Illinois casscountyillinois int %8.0g Cass County, Illinois champaigncountyillinois long %12.0g Champaign County, Illinois christiancountyillinois int %8.0g Christian County, Illinois clarkcountyillinois int %8.0g Clark County, Illinois claycountyillinois int %8.0g Clay County, Illinois clintoncountyillinois int %8.0g Clinton County, Illinois colescountyillinois int %8.0g Coles County, Illinois cookcountyillinois long %12.0g Cook County, Illinois crawfordcountyillinois int %8.0g Crawford County, Illinois cumberlandcountyillinois int %8.0g Cumberland County, Illinois dekalbcountyillinois int %8.0g DeKalb County, Illinois dewittcountyillinois int %8.0g De Witt County, Illinois douglascountyillinois int %8.0g Douglas County, Illinois dupagecountyillinois long %12.0g DuPage County, Illinois edgarcountyillinois int %8.0g Edgar County, Illinois edwardscountyillinois int %8.0g Edwards County, Illinois effinghamcountyillinois int %8.0g Effingham County, Illinois fayettecountyillinois int %8.0g Fayette County, Illinois fordcountyillinois int %8.0g Ford County, Illinois franklincountyillinois int %8.0g Franklin County, Illinois fultoncountyillinois int %8.0g Fulton County, Illinois gallatincountyillinois int %8.0g Gallatin County, Illinois greenecountyillinois int %8.0g Greene County, Illinois grundycountyillinois int %8.0g Grundy County, Illinois hamiltoncountyillinois int %8.0g Hamilton County, Illinois hancockcountyillinois int %8.0g Hancock County, Illinois hardincountyillinois int %8.0g Hardin County, Illinois hendersoncountyillinois int %8.0g Henderson County, Illinois henrycountyillinois int %8.0g Henry County, Illinois iroquoiscountyillinois int %8.0g Iroquois County, Illinois jacksoncountyillinois int %8.0g Jackson County, Illinois jaspercountyillinois int %8.0g Jasper County, Illinois jeffersoncountyillinois int %8.0g Jefferson County, Illinois jerseycountyillinois int %8.0g Jersey County, Illinois jodaviesscountyillinois int %8.0g Jo Daviess County, Illinois johnsoncountyillinois int %8.0g Johnson County, Illinois kanecountyillinois long %12.0g Kane County, Illinois kankakeecountyillinois int %8.0g Kankakee County, Illinois kendallcountyillinois int %8.0g Kendall County, Illinois knoxcountyillinois int %8.0g Knox County, Illinois lakecountyillinois long %12.0g Lake County, Illinois lasallecountyillinois int %8.0g La Salle County, Illinois lawrencecountyillinois int %8.0g Lawrence County, Illinois leecountyillinois int %8.0g Lee County, Illinois livingstoncountyillinois int %8.0g Livingston County, Illinois logancountyillinois int %8.0g Logan County, Illinois mcdonoughcountyillinois int %8.0g McDonough County, Illinois mchenrycountyillinois long %12.0g McHenry County, Illinois mcleancountyillinois long %12.0g McLean County, Illinois maconcountyillinois int %8.0g Macon County, Illinois macoupincountyillinois int %8.0g Macoupin County, Illinois madisoncountyillinois long %12.0g Madison County, Illinois marioncountyillinois int %8.0g Marion County, Illinois marshallcountyillinois int %8.0g Marshall County, Illinois masoncountyillinois int %8.0g Mason County, Illinois massaccountyillinois int %8.0g Massac County, Illinois menardcountyillinois int %8.0g Menard County, Illinois mercercountyillinois int %8.0g Mercer County, Illinois monroecountyillinois int %8.0g Monroe County, Illinois montgomerycountyillinois int %8.0g Montgomery County, Illinois morgancountyillinois int %8.0g Morgan County, Illinois moultriecountyillinois int %8.0g Moultrie County, Illinois oglecountyillinois int %8.0g Ogle County, Illinois peoriacountyillinois long %12.0g Peoria County, Illinois perrycountyillinois int %8.0g Perry County, Illinois piattcountyillinois int %8.0g Piatt County, Illinois pikecountyillinois int %8.0g Pike County, Illinois popecountyillinois int %8.0g Pope County, Illinois pulaskicountyillinois int %8.0g Pulaski County, Illinois putnamcountyillinois int %8.0g Putnam County, Illinois randolphcountyillinois int %8.0g Randolph County, Illinois richlandcountyillinois int %8.0g Richland County, Illinois rockislandcountyillinois long %12.0g Rock Island County, Illinois stclaircountyillinois long %12.0g St. Clair County, Illinois salinecountyillinois int %8.0g Saline County, Illinois sangamoncountyillinois long %12.0g Sangamon County, Illinois schuylercountyillinois int %8.0g Schuyler County, Illinois scottcountyillinois int %8.0g Scott County, Illinois shelbycountyillinois int %8.0g Shelby County, Illinois starkcountyillinois int %8.0g Stark County, Illinois stephensoncountyillinois int %8.0g Stephenson County, Illinois tazewellcountyillinois long %12.0g Tazewell County, Illinois unioncountyillinois int %8.0g Union County, Illinois vermilioncountyillinois int %8.0g Vermilion County, Illinois wabashcountyillinois int %8.0g Wabash County, Illinois warrencountyillinois int %8.0g Warren County, Illinois washingtoncountyillinois int %8.0g Washington County, Illinois waynecountyillinois int %8.0g Wayne County, Illinois whitecountyillinois int %8.0g White County, Illinois whitesidecountyillinois int %8.0g Whiteside County, Illinois willcountyillinois long %12.0g Will County, Illinois williamsoncountyillinois int %8.0g Williamson County, Illinois winnebagocountyillinois long %12.0g Winnebago County, Illinois woodfordcountyillinois int %8.0g Woodford County, Illinois ------------------------------------------------------------------------------- Sorted by: Note: dataset has changed since last saved . summarize ; Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- industry | 0 gender | 0 adamscount~s | 54 1535.167 3193.471 0 17313 alexanderc~s | 54 148.7778 313.8573 0 1701 bondcounty~s | 54 354.7963 735.1878 0 4190 -------------+-------------------------------------------------------- boonecount~s | 54 894.0741 1960.521 0 11071 browncount~s | 54 113.8148 239.1417 0 1349 bureaucoun~s | 54 795.4444 1661.183 0 9274 calhouncou~s | 54 109.3519 225.8502 0 1291 carrollcou~s | 54 349.8519 745.6519 0 4198 -------------+-------------------------------------------------------- casscounty~s | 54 294.5741 622.5137 0 3612 champaignc~s | 54 4531.685 9114.622 0 48427 christianc~s | 54 739.7407 1515.477 0 8412 clarkcount~s | 54 355.3333 766.4639 0 4125 claycounty~s | 54 293.2407 625.5947 0 3450 -------------+-------------------------------------------------------- clintoncou~s | 54 786.1296 1636.962 0 9175 colescount~s | 54 1229.722 2543.914 0 13259 cookcounty~s | 54 114226.9 226531.2 89 1270841 crawfordco~s | 54 384.8889 829.2664 0 4515 cumberland~s | 54 238.5 527.0783 0 2884 -------------+-------------------------------------------------------- dekalbcoun~s | 54 2149.093 4395.746 0 24496 dewittcoun~s | 54 393.463 792.0055 0 4496 douglascou~s | 54 433.4074 917.0845 0 5286 dupagecoun~s | 54 22229.11 44720.79 36 259363 edgarcount~s | 54 402.4259 872.2402 0 4764 -------------+-------------------------------------------------------- edwardscou~s | 54 147.3704 323.1824 0 1795 effinghamc~s | 54 774.3148 1642.813 0 9221 fayettecou~s | 54 422.0741 898.16 0 5069 fordcounty~s | 54 316.6111 654.6102 0 3675 franklinco~s | 54 740.7222 1528.011 0 8468 -------------+-------------------------------------------------------- fultoncoun~s | 54 744.4074 1559.825 0 8620 gallatinco~s | 54 125.037 253.7572 0 1407 greenecoun~s | 54 294.5741 607.8282 0 3367 grundycoun~s | 54 855.2778 1765.118 0 10185 hamiltonco~s | 54 174.9815 363.1192 0 2005 -------------+-------------------------------------------------------- hancockcou~s | 54 456.9259 966.6394 0 5312 hardincoun~s | 54 87.12963 178.7804 0 969 hendersonc~s | 54 183.3148 389.2328 0 2198 henrycount~s | 54 1142.204 2393.052 0 13323 iroquoisco~s | 54 694.7037 1437.036 0 8051 -------------+-------------------------------------------------------- jacksoncou~s | 54 1369.759 2792.445 0 14852 jaspercoun~s | 54 226.3889 471.071 0 2685 jeffersonc~s | 54 804.0556 1681.825 5 9336 jerseycoun~s | 54 482.3148 996.1334 0 5426 jodaviessc~s | 54 538 1089.895 0 6166 -------------+-------------------------------------------------------- johnsoncou~s | 54 207.9444 425.2606 0 2327 kanecounty~s | 54 8918.519 18567.85 12 108349 kankakeeco~s | 54 2217.167 4609.2 0 25395 kendallcou~s | 54 1314.519 2734.943 0 15875 knoxcounty~s | 54 1184.463 2467.916 0 13065 -------------+-------------------------------------------------------- lakecounty~s | 54 14258.94 29354.74 25 171424 lasallecou~s | 54 2323.963 4867.181 4 27526 lawrenceco~s | 54 329.3519 681.7475 0 3708 leecountyi~s | 54 740.3148 1560.317 0 8787 livingston~s | 54 820.9815 1725.332 0 9688 -------------+-------------------------------------------------------- logancount~s | 54 645.3704 1330.406 0 7266 mcdonoughc~s | 54 767.8889 1570.845 0 8109 mchenrycou~s | 54 6112.389 12860.51 40 74972 mcleancoun~s | 54 3941.759 7709.557 0 41497 maconcount~s | 54 2417.019 5007.022 2 27304 -------------+-------------------------------------------------------- macoupinco~s | 54 1050.963 2157.346 0 12212 madisoncou~s | 54 5771.222 11747.82 25 64048 marioncoun~s | 54 858.5741 1847.846 0 9765 marshallco~s | 54 301.037 635.9187 0 3594 masoncount~s | 54 324.1667 680.7004 0 3901 -------------+-------------------------------------------------------- massaccoun~s | 54 317.0926 645.5588 0 3537 menardcoun~s | 54 299.2222 615.1717 0 3373 mercercoun~s | 54 366.0185 767.654 0 4219 monroecoun~s | 54 679.8148 1376.304 0 7711 montgomery~s | 54 597.6852 1226.205 0 6688 -------------+-------------------------------------------------------- morgancoun~s | 54 814.4259 1663.558 0 8940 moultrieco~s | 54 310.0741 672.2127 0 3767 oglecounty~s | 54 1124.37 2380.177 0 13588 peoriacoun~s | 54 3971.278 8139.485 0 43841 perrycount~s | 54 415.5926 885.7843 0 4832 -------------+-------------------------------------------------------- piattcount~s | 54 399.963 813.8064 0 4518 pikecounty~s | 54 360.0741 739.8335 0 4114 popecounty~s | 54 88.2963 180.6493 0 1011 pulaskicou~s | 54 131.8704 276.007 0 1472 putnamcoun~s | 54 128.5556 275.2108 0 1570 -------------+-------------------------------------------------------- randolphco~s | 54 638.463 1341.191 0 7431 richlandco~s | 54 336.0556 698.4208 0 3860 rockisland~s | 54 3274.981 6756.811 15 36670 stclaircou~s | 54 5215.833 10423.33 8 55560 salinecoun~s | 54 503.0556 1026.988 0 5470 -------------+-------------------------------------------------------- sangamonco~s | 54 4523.259 9258.278 13 48984 schuylerco~s | 54 170.1667 347.7377 0 1930 scottcount~s | 54 124.6667 261.8683 0 1424 shelbycoun~s | 54 483.9444 1037.297 0 5892 starkcount~s | 54 122.8519 257.3207 0 1459 -------------+-------------------------------------------------------- stephenson~s | 54 1071.13 2268.814 0 12082 tazewellco~s | 54 2895.093 6041.903 0 33685 unioncount~s | 54 360.6481 747.132 0 3877 vermilionc~s | 54 1620.037 3423.332 0 18744 wabashcoun~s | 54 286.6296 596.6173 0 3409 -------------+-------------------------------------------------------- warrencoun~s | 54 431.4444 886.189 0 4798 washington~s | 54 355.963 730.684 0 4201 waynecount~s | 54 344.7593 747.3299 0 4183 whitecount~s | 54 316.463 651.4038 0 3687 whitesidec~s | 54 1267.648 2757.2 0 14980 -------------+-------------------------------------------------------- willcounty~s | 54 11392.52 23248.99 37 134807 williamson~s | 54 1269.981 2600.794 0 14189 winnebagoc~s | 54 6082.019 13069.23 0 71103 woodfordco~s | 54 822.963 1702.947 0 9741 . *************************************************************************************** ; . * goal is creation of dataset with rows corresponding to counties, with variables * ; . * named as "Manufacturing", "Construction", and so on, containing employment levels * ; . * and then to compute some specialization indexes * ; . *************************************************************************************** ; . * let i denote county i = 1 to 102 * ; . * j denote industry j = 1 to 27 * ; . * k denote gender k = 1 to 2 * ; . ***************************************************************************** ; . * current data set.... * ; . * each row corresponds to a unique (j,k) pair * ; . * variables (columns) are the counties (i) * ; . *********************************************************************************************** ; . * ultimately want a data set w/rows for counties, columns for industries, w/gender "dumped" * ; . *********************************************************************************************** ; . * Step 1: collapse to get rid of gender, because it's a nuisance * ; . * ...this means adding up Male & Female employment to get Total * ; . *************************************************************************************** ; . sort industry; . collapse (sum) *illinois , by(industry) ; . generate short_industry = substr(industry,1,20); . describe, fullnames; Contains data obs: 27 vars: 104 size: 24,948 (99.8% of memory free) ------------------------------------------------------------------------------- storage display value variable name type format label variable label ------------------------------------------------------------------------------- industry str84 %84s adamscountyillinois double %8.0g (sum) adamscountyillinois alexandercountyillinois double %8.0g (sum) alexandercountyillinois bondcountyillinois double %8.0g (sum) bondcountyillinois boonecountyillinois double %8.0g (sum) boonecountyillinois browncountyillinois double %8.0g (sum) browncountyillinois bureaucountyillinois double %8.0g (sum) bureaucountyillinois calhouncountyillinois double %8.0g (sum) calhouncountyillinois carrollcountyillinois double %8.0g (sum) carrollcountyillinois casscountyillinois double %8.0g (sum) casscountyillinois champaigncountyillinois double %12.0g (sum) champaigncountyillinois christiancountyillinois double %8.0g (sum) christiancountyillinois clarkcountyillinois double %8.0g (sum) clarkcountyillinois claycountyillinois double %8.0g (sum) claycountyillinois clintoncountyillinois double %8.0g (sum) clintoncountyillinois colescountyillinois double %8.0g (sum) colescountyillinois cookcountyillinois double %12.0g (sum) cookcountyillinois crawfordcountyillinois double %8.0g (sum) crawfordcountyillinois cumberlandcountyillinois double %8.0g (sum) cumberlandcountyillinois dekalbcountyillinois double %8.0g (sum) dekalbcountyillinois dewittcountyillinois double %8.0g (sum) dewittcountyillinois douglascountyillinois double %8.0g (sum) douglascountyillinois dupagecountyillinois double %12.0g (sum) dupagecountyillinois edgarcountyillinois double %8.0g (sum) edgarcountyillinois edwardscountyillinois double %8.0g (sum) edwardscountyillinois effinghamcountyillinois double %8.0g (sum) effinghamcountyillinois fayettecountyillinois double %8.0g (sum) fayettecountyillinois fordcountyillinois double %8.0g (sum) fordcountyillinois franklincountyillinois double %8.0g (sum) franklincountyillinois fultoncountyillinois double %8.0g (sum) fultoncountyillinois gallatincountyillinois double %8.0g (sum) gallatincountyillinois greenecountyillinois double %8.0g (sum) greenecountyillinois grundycountyillinois double %8.0g (sum) grundycountyillinois hamiltoncountyillinois double %8.0g (sum) hamiltoncountyillinois hancockcountyillinois double %8.0g (sum) hancockcountyillinois hardincountyillinois double %8.0g (sum) hardincountyillinois hendersoncountyillinois double %8.0g (sum) hendersoncountyillinois henrycountyillinois double %8.0g (sum) henrycountyillinois iroquoiscountyillinois double %8.0g (sum) iroquoiscountyillinois jacksoncountyillinois double %8.0g (sum) jacksoncountyillinois jaspercountyillinois double %8.0g (sum) jaspercountyillinois jeffersoncountyillinois double %8.0g (sum) jeffersoncountyillinois jerseycountyillinois double %8.0g (sum) jerseycountyillinois jodaviesscountyillinois double %8.0g (sum) jodaviesscountyillinois johnsoncountyillinois double %8.0g (sum) johnsoncountyillinois kanecountyillinois double %12.0g (sum) kanecountyillinois kankakeecountyillinois double %8.0g (sum) kankakeecountyillinois kendallcountyillinois double %8.0g (sum) kendallcountyillinois knoxcountyillinois double %8.0g (sum) knoxcountyillinois lakecountyillinois double %12.0g (sum) lakecountyillinois lasallecountyillinois double %8.0g (sum) lasallecountyillinois lawrencecountyillinois double %8.0g (sum) lawrencecountyillinois leecountyillinois double %8.0g (sum) leecountyillinois livingstoncountyillinois double %8.0g (sum) livingstoncountyillinois logancountyillinois double %8.0g (sum) logancountyillinois mcdonoughcountyillinois double %8.0g (sum) mcdonoughcountyillinois mchenrycountyillinois double %12.0g (sum) mchenrycountyillinois mcleancountyillinois double %12.0g (sum) mcleancountyillinois maconcountyillinois double %8.0g (sum) maconcountyillinois macoupincountyillinois double %8.0g (sum) macoupincountyillinois madisoncountyillinois double %12.0g (sum) madisoncountyillinois marioncountyillinois double %8.0g (sum) marioncountyillinois marshallcountyillinois double %8.0g (sum) marshallcountyillinois masoncountyillinois double %8.0g (sum) masoncountyillinois massaccountyillinois double %8.0g (sum) massaccountyillinois menardcountyillinois double %8.0g (sum) menardcountyillinois mercercountyillinois double %8.0g (sum) mercercountyillinois monroecountyillinois double %8.0g (sum) monroecountyillinois montgomerycountyillinois double %8.0g (sum) montgomerycountyillinois morgancountyillinois double %8.0g (sum) morgancountyillinois moultriecountyillinois double %8.0g (sum) moultriecountyillinois oglecountyillinois double %8.0g (sum) oglecountyillinois peoriacountyillinois double %12.0g (sum) peoriacountyillinois perrycountyillinois double %8.0g (sum) perrycountyillinois piattcountyillinois double %8.0g (sum) piattcountyillinois pikecountyillinois double %8.0g (sum) pikecountyillinois popecountyillinois double %8.0g (sum) popecountyillinois pulaskicountyillinois double %8.0g (sum) pulaskicountyillinois putnamcountyillinois double %8.0g (sum) putnamcountyillinois randolphcountyillinois double %8.0g (sum) randolphcountyillinois richlandcountyillinois double %8.0g (sum) richlandcountyillinois rockislandcountyillinois double %12.0g (sum) rockislandcountyillinois stclaircountyillinois double %12.0g (sum) stclaircountyillinois salinecountyillinois double %8.0g (sum) salinecountyillinois sangamoncountyillinois double %12.0g (sum) sangamoncountyillinois schuylercountyillinois double %8.0g (sum) schuylercountyillinois scottcountyillinois double %8.0g (sum) scottcountyillinois shelbycountyillinois double %8.0g (sum) shelbycountyillinois starkcountyillinois double %8.0g (sum) starkcountyillinois stephensoncountyillinois double %8.0g (sum) stephensoncountyillinois tazewellcountyillinois double %12.0g (sum) tazewellcountyillinois unioncountyillinois double %8.0g (sum) unioncountyillinois vermilioncountyillinois double %8.0g (sum) vermilioncountyillinois wabashcountyillinois double %8.0g (sum) wabashcountyillinois warrencountyillinois double %8.0g (sum) warrencountyillinois washingtoncountyillinois double %8.0g (sum) washingtoncountyillinois waynecountyillinois double %8.0g (sum) waynecountyillinois whitecountyillinois double %8.0g (sum) whitecountyillinois whitesidecountyillinois double %8.0g (sum) whitesidecountyillinois willcountyillinois double %12.0g (sum) willcountyillinois williamsoncountyillinois double %8.0g (sum) williamsoncountyillinois winnebagocountyillinois double %12.0g (sum) winnebagocountyillinois woodfordcountyillinois double %8.0g (sum) woodfordcountyillinois short_industry str20 %20s ------------------------------------------------------------------------------- Sorted by: industry Note: dataset has changed since last saved . summarize; Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- industry | 0 adamscount~s | 27 3070.333 6309.396 5 33344 alexanderc~s | 27 297.5556 616.8975 3 3256 bondcounty~s | 27 709.5926 1453.256 0 7718 boonecount~s | 27 1788.148 3871.717 1 20149 -------------+-------------------------------------------------------- browncount~s | 27 227.6296 472.2495 0 2528 bureaucoun~s | 27 1590.889 3279.379 4 17395 calhouncou~s | 27 218.7037 436.5392 0 2332 carrollcou~s | 27 699.7037 1466.035 2 7724 casscounty~s | 27 589.1481 1219.429 0 6476 -------------+-------------------------------------------------------- champaignc~s | 27 9063.37 18268.43 29 93236 christianc~s | 27 1479.481 2980.677 0 15941 clarkcount~s | 27 710.6667 1512.242 8 7890 claycounty~s | 27 586.4815 1240.587 6 6531 clintoncou~s | 27 1572.259 3222.509 4 17153 -------------+-------------------------------------------------------- colescount~s | 27 2459.444 5059.095 0 26381 cookcounty~s | 27 228453.7 452458.6 528 2421287 crawfordco~s | 27 769.7778 1647.583 0 8594 cumberland~s | 27 477 1040.483 0 5411 dekalbcoun~s | 27 4298.185 8744.583 24 45909 -------------+-------------------------------------------------------- dewittcoun~s | 27 786.9259 1560.379 0 8380 douglascou~s | 27 866.8148 1811.739 0 9571 dupagecoun~s | 27 44458.22 89089.16 191 476172 edgarcount~s | 27 804.8519 1711.56 0 8934 edwardscou~s | 27 294.7407 640.5327 0 3314 -------------+-------------------------------------------------------- effinghamc~s | 27 1548.63 3250.025 0 17234 fayettecou~s | 27 844.1481 1768.887 0 9356 fordcounty~s | 27 633.2222 1293.914 0 6883 franklinco~s | 27 1481.444 3012.574 0 16017 fultoncoun~s | 27 1488.815 3049.167 2 16137 -------------+-------------------------------------------------------- gallatinco~s | 27 250.0741 490.824 3 2600 greenecoun~s | 27 589.1481 1187.859 0 6301 grundycoun~s | 27 1710.556 3473.755 0 18567 hamiltonco~s | 27 349.963 705.9355 7 3698 hancockcou~s | 27 913.8519 1906.244 0 10016 -------------+-------------------------------------------------------- hardincoun~s | 27 174.2593 342.4897 0 1788 hendersonc~s | 27 366.6296 761.5041 0 4005 henrycount~s | 27 2284.407 4709.411 12 24999 iroquoisco~s | 27 1389.407 2830.453 4 14980 jacksoncou~s | 27 2739.519 5578.821 4 28213 -------------+-------------------------------------------------------- jaspercoun~s | 27 452.7778 927.8617 0 4935 jeffersonc~s | 27 1608.111 3326.688 11 17592 jerseycoun~s | 27 964.6296 1955.808 2 10348 jodaviessc~s | 27 1076 2158.257 8 11528 johnsoncou~s | 27 415.8889 833.5283 0 4398 -------------+-------------------------------------------------------- kanecounty~s | 27 17837.04 36855.44 160 196184 kankakeeco~s | 27 4434.333 9125.729 0 48227 kendallcou~s | 27 2629.037 5400.787 20 28842 knoxcounty~s | 27 2368.926 4868.321 0 25485 lakecounty~s | 27 28517.89 58407.31 62 310396 -------------+-------------------------------------------------------- lasallecou~s | 27 4647.926 9603.211 11 51042 lawrenceco~s | 27 658.7037 1338.274 0 7042 leecountyi~s | 27 1480.63 3083.739 0 16325 livingston~s | 27 1641.963 3390.479 0 17952 logancount~s | 27 1290.741 2634.695 2 13932 -------------+-------------------------------------------------------- mcdonoughc~s | 27 1535.778 3127.839 0 15934 mchenrycou~s | 27 12224.78 25464.55 206 135269 mcleancoun~s | 27 7883.519 15397.32 8 80759 maconcount~s | 27 4834.037 9907.095 12 52584 macoupinco~s | 27 2101.926 4246.384 2 22672 -------------+-------------------------------------------------------- madisoncou~s | 27 11542.44 23242.2 112 123468 marioncoun~s | 27 1717.148 3650.703 0 18873 marshallco~s | 27 602.0741 1242.334 0 6568 masoncount~s | 27 648.3333 1327.137 0 7087 massaccoun~s | 27 634.1852 1262.55 0 6730 -------------+-------------------------------------------------------- menardcoun~s | 27 598.4444 1218.809 0 6486 mercercoun~s | 27 732.037 1506.455 0 8031 monroecoun~s | 27 1359.63 2708.731 0 14526 montgomery~s | 27 1195.37 2420.089 5 12951 morgancoun~s | 27 1628.852 3295.653 0 17425 -------------+-------------------------------------------------------- moultrieco~s | 27 620.1481 1323.633 6 6927 oglecounty~s | 27 2248.741 4693.266 18 24812 peoriacoun~s | 27 7942.556 16140.11 30 85258 perrycount~s | 27 831.1852 1748.671 0 9197 piattcount~s | 27 799.9259 1607.282 0 8502 -------------+-------------------------------------------------------- pikecounty~s | 27 720.1481 1447.531 0 7718 popecounty~s | 27 176.5926 354.2339 0 1885 pulaskicou~s | 27 263.7407 541.596 0 2848 putnamcoun~s | 27 257.1111 539.528 0 2855 randolphco~s | 27 1276.926 2645.013 6 13993 -------------+-------------------------------------------------------- richlandco~s | 27 672.1111 1378.328 0 7270 rockisland~s | 27 6549.963 13422.56 32 71446 stclaircou~s | 27 10431.67 20692.75 56 110505 salinecoun~s | 27 1006.111 2015 0 10650 sangamonco~s | 27 9046.519 18458.1 49 97526 -------------+-------------------------------------------------------- schuylerco~s | 27 340.3333 681.4442 0 3621 scottcount~s | 27 249.3333 513.8181 0 2736 shelbycoun~s | 27 967.8889 2034.936 6 10698 starkcount~s | 27 245.7037 502.1695 0 2658 stephenson~s | 27 2142.259 4496.621 1 23536 -------------+-------------------------------------------------------- tazewellco~s | 27 5790.185 11894.22 28 62984 unioncount~s | 27 721.2963 1473.494 0 7581 vermilionc~s | 27 3240.074 6771.915 2 35735 wabashcoun~s | 27 573.2593 1173.993 0 6265 warrencoun~s | 27 862.8889 1747.304 0 9205 -------------+-------------------------------------------------------- washington~s | 27 711.9259 1438.649 2 7669 waynecount~s | 27 689.5185 1472.393 0 7649 whitecount~s | 27 632.9259 1271.765 3 6772 whitesidec~s | 27 2535.296 5452.009 0 28343 willcounty~s | 27 22785.04 46028.94 207 246431 -------------+-------------------------------------------------------- williamson~s | 27 2539.963 5161.011 0 27266 winnebagoc~s | 27 12164.04 25857.82 68 134952 woodfordco~s | 27 1645.926 3342.273 4 17681 short_indu~y | 0 . /* print out cook county figures to take a look...should have nobs = # of industries */ > /* ....with columns (variables) corresponding to different counties */ > /* ....and with the contents of those variables equalling total employment */ > /* ....in industry i and county j */ > list short_industry cookcountyillinois* ; +---------------------------------+ | short_industry cookco~s | |---------------------------------| 1. | Accommodation and fo 138563 | 2. | Administrative and s 102300 | 3. | Agriculture, forestr 1828 | 4. | Agriculture, forestr 2356 | 5. | Arts, entertainment, 41029 | |---------------------------------| 6. | Arts, entertainment, 179592 | 7. | Construction 119355 | 8. | Educational services 194308 | 9. | Educational, health 454951 | 10. | Finance and insuranc 168194 | |---------------------------------| 11. | Finance, insurance, 219831 | 12. | Health care and soci 260643 | 13. | Information 82835 | 14. | Management of compan 1863 | 15. | Manufacturing 342422 | |---------------------------------| 16. | Mining 528 | 17. | Other services (exce 120337 | 18. | Professional, scient 202319 | 19. | Professional, scient 306482 | 20. | Public administratio 93611 | |---------------------------------| 21. | Real estate and rent 51637 | 22. | Retail trade 244344 | 23. | Total 2421287 | 24. | Transportation and w 149391 | 25. | Transportation and w 162465 | |---------------------------------| 26. | Utilities 13074 | 27. | Wholesale trade 92706 | +---------------------------------+ . ******************************************************************************** ; . * Step 2: keep only the 20 industries that correspond to published report * ; . * ....note....La Plata Colorado report has only 19 (it combines two), but * ; . * we will use 20 for now....this will be close enough to verify figures * ; . ******************************************************************************** ; . sort industry; . merge industry using tempdata; . tabulate _merge; _merge | Freq. Percent Cum. ------------+----------------------------------- 1 | 7 25.93 25.93 3 | 20 74.07 100.00 ------------+----------------------------------- Total | 27 100.00 . /* keep only industries that are on my list */ > keep if _merge == 3; (7 observations deleted) . sort ind_num; . /* example: print out data for Cook County */ > list short_industry ind_num cook*; +-------------------------------------------+ | short_industry ind_num cookco~s | |-------------------------------------------| 1. | Agriculture, forestr 1 1828 | 2. | Mining 2 528 | 3. | Construction 3 119355 | 4. | Manufacturing 4 342422 | 5. | Wholesale trade 5 92706 | |-------------------------------------------| 6. | Retail trade 6 244344 | 7. | Transportation and w 7 149391 | 8. | Utilities 8 13074 | 9. | Information 9 82835 | 10. | Finance and insuranc 10 168194 | |-------------------------------------------| 11. | Real estate and rent 11 51637 | 12. | Professional, scient 12 202319 | 13. | Management of compan 13 1863 | 14. | Administrative and s 14 102300 | 15. | Educational services 15 194308 | |-------------------------------------------| 16. | Health care and soci 16 260643 | 17. | Arts, entertainment, 17 41029 | 18. | Accommodation and fo 18 138563 | 19. | Other services (exce 19 120337 | 20. | Public administratio 20 93611 | +-------------------------------------------+ . ***************************************************************************************** ; . * Step 3: reshape long....move counties from columns to rows....each observation * ; . * will now correspond to a unique (i,j) pair [industry, county] * ; . ***************************************************************************************** ; . * to use reshape command, I must rename my variables (columns).... * ; . * current names are name+"countyillinois" * ; . * new names should be "county"+name * ; . ************************************************************************* ; . renvars *illinois , map("county"+substr("@",1,strpos("@","countyillinois")-1)) display; adamscountyillinois -> countyadams alexandercountyillinois -> countyalexander bondcountyillinois -> countybond boonecountyillinois -> countyboone browncountyillinois -> countybrown bureaucountyillinois -> countybureau calhouncountyillinois -> countycalhoun carrollcountyillinois -> countycarroll casscountyillinois -> countycass champaigncountyillinois -> countychampaign christiancountyillinois -> countychristian clarkcountyillinois -> countyclark claycountyillinois -> countyclay clintoncountyillinois -> countyclinton colescountyillinois -> countycoles cookcountyillinois -> countycook crawfordcountyillinois -> countycrawford cumberlandcountyillinois -> countycumberland dekalbcountyillinois -> countydekalb dewittcountyillinois -> countydewitt douglascountyillinois -> countydouglas dupagecountyillinois -> countydupage edgarcountyillinois -> countyedgar edwardscountyillinois -> countyedwards effinghamcountyillinois -> countyeffingham fayettecountyillinois -> countyfayette fordcountyillinois -> countyford franklincountyillinois -> countyfranklin fultoncountyillinois -> countyfulton gallatincountyillinois -> countygallatin greenecountyillinois -> countygreene grundycountyillinois -> countygrundy hamiltoncountyillinois -> countyhamilton hancockcountyillinois -> countyhancock hardincountyillinois -> countyhardin hendersoncountyillinois -> countyhenderson henrycountyillinois -> countyhenry iroquoiscountyillinois -> countyiroquois jacksoncountyillinois -> countyjackson jaspercountyillinois -> countyjasper jeffersoncountyillinois -> countyjefferson jerseycountyillinois -> countyjersey jodaviesscountyillinois -> countyjodaviess johnsoncountyillinois -> countyjohnson kanecountyillinois -> countykane kankakeecountyillinois -> countykankakee kendallcountyillinois -> countykendall knoxcountyillinois -> countyknox lakecountyillinois -> countylake lasallecountyillinois -> countylasalle lawrencecountyillinois -> countylawrence leecountyillinois -> countylee livingstoncountyillinois -> countylivingston logancountyillinois -> countylogan mcdonoughcountyillinois -> countymcdonough mchenrycountyillinois -> countymchenry mcleancountyillinois -> countymclean maconcountyillinois -> countymacon macoupincountyillinois -> countymacoupin madisoncountyillinois -> countymadison marioncountyillinois -> countymarion marshallcountyillinois -> countymarshall masoncountyillinois -> countymason massaccountyillinois -> countymassac menardcountyillinois -> countymenard mercercountyillinois -> countymercer monroecountyillinois -> countymonroe montgomerycountyillinois -> countymontgomery morgancountyillinois -> countymorgan moultriecountyillinois -> countymoultrie oglecountyillinois -> countyogle peoriacountyillinois -> countypeoria perrycountyillinois -> countyperry piattcountyillinois -> countypiatt pikecountyillinois -> countypike popecountyillinois -> countypope pulaskicountyillinois -> countypulaski putnamcountyillinois -> countyputnam randolphcountyillinois -> countyrandolph richlandcountyillinois -> countyrichland rockislandcountyillinois -> countyrockisland stclaircountyillinois -> countystclair salinecountyillinois -> countysaline sangamoncountyillinois -> countysangamon schuylercountyillinois -> countyschuyler scottcountyillinois -> countyscott shelbycountyillinois -> countyshelby starkcountyillinois -> countystark stephensoncountyillinois -> countystephenson tazewellcountyillinois -> countytazewell unioncountyillinois -> countyunion vermilioncountyillinois -> countyvermilion wabashcountyillinois -> countywabash warrencountyillinois -> countywarren washingtoncountyillinois -> countywashington waynecountyillinois -> countywayne whitecountyillinois -> countywhite whitesidecountyillinois -> countywhiteside willcountyillinois -> countywill williamsoncountyillinois -> countywilliamson winnebagocountyillinois -> countywinnebago woodfordcountyillinois -> countywoodford . describe, fullnames; Contains data obs: 20 vars: 108 size: 18,560 (99.8% of memory free) ------------------------------------------------------------------------------- storage display value variable name type format label variable label ------------------------------------------------------------------------------- industry str84 %84s countyadams double %8.0g (sum) adamscountyillinois countyalexander double %8.0g (sum) alexandercountyillinois countybond double %8.0g (sum) bondcountyillinois countyboone double %8.0g (sum) boonecountyillinois countybrown double %8.0g (sum) browncountyillinois countybureau double %8.0g (sum) bureaucountyillinois countycalhoun double %8.0g (sum) calhouncountyillinois countycarroll double %8.0g (sum) carrollcountyillinois countycass double %8.0g (sum) casscountyillinois countychampaign double %12.0g (sum) champaigncountyillinois countychristian double %8.0g (sum) christiancountyillinois countyclark double %8.0g (sum) clarkcountyillinois countyclay double %8.0g (sum) claycountyillinois countyclinton double %8.0g (sum) clintoncountyillinois countycoles double %8.0g (sum) colescountyillinois countycook double %12.0g (sum) cookcountyillinois countycrawford double %8.0g (sum) crawfordcountyillinois countycumberland double %8.0g (sum) cumberlandcountyillinois countydekalb double %8.0g (sum) dekalbcountyillinois countydewitt double %8.0g (sum) dewittcountyillinois countydouglas double %8.0g (sum) douglascountyillinois countydupage double %12.0g (sum) dupagecountyillinois countyedgar double %8.0g (sum) edgarcountyillinois countyedwards double %8.0g (sum) edwardscountyillinois countyeffingham double %8.0g (sum) effinghamcountyillinois countyfayette double %8.0g (sum) fayettecountyillinois countyford double %8.0g (sum) fordcountyillinois countyfranklin double %8.0g (sum) franklincountyillinois countyfulton double %8.0g (sum) fultoncountyillinois countygallatin double %8.0g (sum) gallatincountyillinois countygreene double %8.0g (sum) greenecountyillinois countygrundy double %8.0g (sum) grundycountyillinois countyhamilton double %8.0g (sum) hamiltoncountyillinois countyhancock double %8.0g (sum) hancockcountyillinois countyhardin double %8.0g (sum) hardincountyillinois countyhenderson double %8.0g (sum) hendersoncountyillinois countyhenry double %8.0g (sum) henrycountyillinois countyiroquois double %8.0g (sum) iroquoiscountyillinois countyjackson double %8.0g (sum) jacksoncountyillinois countyjasper double %8.0g (sum) jaspercountyillinois countyjefferson double %8.0g (sum) jeffersoncountyillinois countyjersey double %8.0g (sum) jerseycountyillinois countyjodaviess double %8.0g (sum) jodaviesscountyillinois countyjohnson double %8.0g (sum) johnsoncountyillinois countykane double %12.0g (sum) kanecountyillinois countykankakee double %8.0g (sum) kankakeecountyillinois countykendall double %8.0g (sum) kendallcountyillinois countyknox double %8.0g (sum) knoxcountyillinois countylake double %12.0g (sum) lakecountyillinois countylasalle double %8.0g (sum) lasallecountyillinois countylawrence double %8.0g (sum) lawrencecountyillinois countylee double %8.0g (sum) leecountyillinois countylivingston double %8.0g (sum) livingstoncountyillinois countylogan double %8.0g (sum) logancountyillinois countymcdonough double %8.0g (sum) mcdonoughcountyillinois countymchenry double %12.0g (sum) mchenrycountyillinois countymclean double %12.0g (sum) mcleancountyillinois countymacon double %8.0g (sum) maconcountyillinois countymacoupin double %8.0g (sum) macoupincountyillinois countymadison double %12.0g (sum) madisoncountyillinois countymarion double %8.0g (sum) marioncountyillinois countymarshall double %8.0g (sum) marshallcountyillinois countymason double %8.0g (sum) masoncountyillinois countymassac double %8.0g (sum) massaccountyillinois countymenard double %8.0g (sum) menardcountyillinois countymercer double %8.0g (sum) mercercountyillinois countymonroe double %8.0g (sum) monroecountyillinois countymontgomery double %8.0g (sum) montgomerycountyillinois countymorgan double %8.0g (sum) morgancountyillinois countymoultrie double %8.0g (sum) moultriecountyillinois countyogle double %8.0g (sum) oglecountyillinois countypeoria double %12.0g (sum) peoriacountyillinois countyperry double %8.0g (sum) perrycountyillinois countypiatt double %8.0g (sum) piattcountyillinois countypike double %8.0g (sum) pikecountyillinois countypope double %8.0g (sum) popecountyillinois countypulaski double %8.0g (sum) pulaskicountyillinois countyputnam double %8.0g (sum) putnamcountyillinois countyrandolph double %8.0g (sum) randolphcountyillinois countyrichland double %8.0g (sum) richlandcountyillinois countyrockisland double %12.0g (sum) rockislandcountyillinois countystclair double %12.0g (sum) stclaircountyillinois countysaline double %8.0g (sum) salinecountyillinois countysangamon double %12.0g (sum) sangamoncountyillinois countyschuyler double %8.0g (sum) schuylercountyillinois countyscott double %8.0g (sum) scottcountyillinois countyshelby double %8.0g (sum) shelbycountyillinois countystark double %8.0g (sum) starkcountyillinois countystephenson double %8.0g (sum) stephensoncountyillinois countytazewell double %12.0g (sum) tazewellcountyillinois countyunion double %8.0g (sum) unioncountyillinois countyvermilion double %8.0g (sum) vermilioncountyillinois countywabash double %8.0g (sum) wabashcountyillinois countywarren double %8.0g (sum) warrencountyillinois countywashington double %8.0g (sum) washingtoncountyillinois countywayne double %8.0g (sum) waynecountyillinois countywhite double %8.0g (sum) whitecountyillinois countywhiteside double %8.0g (sum) whitesidecountyillinois countywill double %12.0g (sum) willcountyillinois countywilliamson double %8.0g (sum) williamsoncountyillinois countywinnebago double %12.0g (sum) winnebagocountyillinois countywoodford double %8.0g (sum) woodfordcountyillinois short_industry str20 %20s ind_num byte %8.0g v3 byte %8.0g v4 str1 %9s _merge byte %8.0g ------------------------------------------------------------------------------- Sorted by: ind_num Note: dataset has changed since last saved . reshape long county, i(industry) j(countyname) string ; (note: j = adams alexander bond boone brown bureau calhoun carroll cass champaign christian clark clay clinton coles cook cr > awford cumberland dekalb dewitt douglas dupage edgar edwards effingham fayette ford franklin fulton gallatin greene grundy > hamilton hancock hardin henderson henry iroquois jackson jasper jefferson jersey jodaviess johnson kane kankakee kendall > knox lake lasalle lawrence lee livingston logan macon macoupin madison marion marshall mason massac mcdonough mchenry mcle > an menard mercer monroe montgomery morgan moultrie ogle peoria perry piatt pike pope pulaski putnam randolph richland rock > island saline sangamon schuyler scott shelby stark stclair stephenson tazewell union vermilion wabash warren washington wa > yne white whiteside will williamson winnebago woodford) Data wide -> long ----------------------------------------------------------------------------- Number of obs. 20 -> 2040 Number of variables 108 -> 8 j variable (102 values) -> countyname xij variables: countyadams countyalexander ... countywoodford->county ----------------------------------------------------------------------------- . describe, fullnames; Contains data obs: 2,040 vars: 8 size: 265,200 (97.5% of memory free) ------------------------------------------------------------------------------- storage display value variable name type format label variable label ------------------------------------------------------------------------------- industry str84 %84s countyname str10 %10s county double %8.0g short_industry str20 %20s ind_num byte %8.0g v3 byte %8.0g v4 str1 %9s _merge byte %8.0g ------------------------------------------------------------------------------- Sorted by: industry countyname Note: dataset has changed since last saved . summarize ; Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- industry | 0 countyname | 0 county | 2040 2859.404 15560.03 0 342422 short_indu~y | 0 ind_num | 2040 10.5 5.767695 1 20 -------------+-------------------------------------------------------- v3 | 0 v4 | 0 _merge | 2040 3 0 3 3 . /* drop these variables because ow the next reshape command will bomb..... */ > drop short_industry ind_num v3 v4 _merge; . rename county employment; . sort countyname; . list countyname industry employment in 1/50; +---------------------------------------------------------------------------------+ | countyn~e industry employ~t | |---------------------------------------------------------------------------------| 1. | adams Information 839 | 2. | adams Transportation and warehousing 1265 | 3. | adams Real estate and rental and leasing 484 | 4. | adams Wholesale trade 1485 | 5. | adams Retail trade 4592 | |---------------------------------------------------------------------------------| 6. | adams Agriculture, forestry, fishing and hunting 944 | 7. | adams Health care and social assistance 4776 | 8. | adams Accommodation and food services 2281 | 9. | adams Educational services 2782 | 10. | adams Construction 1921 | |---------------------------------------------------------------------------------| 11. | adams Mining 113 | 12. | adams Arts, entertainment, and recreation 399 | 13. | adams Professional, scientific, and technical services 583 | 14. | adams Management of companies and enterprises 5 | 15. | adams Other services (except public administration) 1693 | |---------------------------------------------------------------------------------| 16. | adams Utilities 221 | 17. | adams Finance and insurance 1399 | 18. | adams Manufacturing 5525 | 19. | adams Public administration 1078 | 20. | adams Administrative and support and waste management services 959 | |---------------------------------------------------------------------------------| 21. | alexander Mining 18 | 22. | alexander Public administration 349 | 23. | alexander Finance and insurance 46 | 24. | alexander Management of companies and enterprises 3 | 25. | alexander Utilities 62 | |---------------------------------------------------------------------------------| 26. | alexander Information 57 | 27. | alexander Accommodation and food services 220 | 28. | alexander Administrative and support and waste management services 94 | 29. | alexander Other services (except public administration) 166 | 30. | alexander Educational services 319 | |---------------------------------------------------------------------------------| 31. | alexander Arts, entertainment, and recreation 20 | 32. | alexander Real estate and rental and leasing 26 | 33. | alexander Retail trade 321 | 34. | alexander Transportation and warehousing 166 | 35. | alexander Construction 233 | |---------------------------------------------------------------------------------| 36. | alexander Health care and social assistance 437 | 37. | alexander Manufacturing 509 | 38. | alexander Wholesale trade 99 | 39. | alexander Professional, scientific, and technical services 26 | 40. | alexander Agriculture, forestry, fishing and hunting 85 | |---------------------------------------------------------------------------------| 41. | bond Administrative and support and waste management services 170 | 42. | bond Accommodation and food services 422 | 43. | bond Health care and social assistance 836 | 44. | bond Agriculture, forestry, fishing and hunting 368 | 45. | bond Construction 445 | |---------------------------------------------------------------------------------| 46. | bond Mining 12 | 47. | bond Retail trade 805 | 48. | bond Public administration 529 | 49. | bond Information 96 | 50. | bond Arts, entertainment, and recreation 14 | +---------------------------------------------------------------------------------+ . ********************************************************************** ; . * Step 4: reshape wide....get industries into columns (variables) * ; . * ...problem is my strings....use ind_num instead? * ; . * ...I can always get those nice labels back via merging * ; . ********************************************************************** ; . sort industry; . merge industry using tempdata; variable industry does not uniquely identify observations in the master data . keep if _merge == 3; (0 observations deleted) . sort ind_num; . list in 1/10; +-------------------------------------------------------------------------------------------------+ | industry countyname employ~t ind_num v3 v4 _merge | |-------------------------------------------------------------------------------------------------| 1. | Agriculture, forestry, fishing and hunting wabash 284 1 . 3 | 2. | Agriculture, forestry, fishing and hunting whiteside 780 1 . 3 | 3. | Agriculture, forestry, fishing and hunting clinton 788 1 . 3 | 4. | Agriculture, forestry, fishing and hunting montgomery 541 1 . 3 | 5. | Agriculture, forestry, fishing and hunting cumberland 234 1 . 3 | |-------------------------------------------------------------------------------------------------| 6. | Agriculture, forestry, fishing and hunting jackson 423 1 . 3 | 7. | Agriculture, forestry, fishing and hunting cook 1828 1 . 3 | 8. | Agriculture, forestry, fishing and hunting bond 368 1 . 3 | 9. | Agriculture, forestry, fishing and hunting kane 1036 1 . 3 | 10. | Agriculture, forestry, fishing and hunting brown 226 1 . 3 | +-------------------------------------------------------------------------------------------------+ . drop v3 v4 _merge industry; . reshape wide employment, j(ind_num) i(countyname); (note: j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20) Data long -> wide ----------------------------------------------------------------------------- Number of obs. 2040 -> 102 Number of variables 3 -> 21 j variable (20 values) ind_num -> (dropped) xij variables: employment -> employment1 employment2 ... employment20 ----------------------------------------------------------------------------- . describe; Contains data obs: 102 vars: 21 size: 17,748 (99.8% of memory free) ------------------------------------------------------------------------------- storage display value variable name type format label variable label ------------------------------------------------------------------------------- countyname str10 %10s employment1 double %8.0g 1 employment employment2 double %8.0g 2 employment employment3 double %8.0g 3 employment employment4 double %8.0g 4 employment employment5 double %8.0g 5 employment employment6 double %8.0g 6 employment employment7 double %8.0g 7 employment employment8 double %8.0g 8 employment employment9 double %8.0g 9 employment employment10 double %8.0g 10 employment employment11 double %8.0g 11 employment employment12 double %8.0g 12 employment employment13 double %8.0g 13 employment employment14 double %8.0g 14 employment employment15 double %8.0g 15 employment employment16 double %8.0g 16 employment employment17 double %8.0g 17 employment employment18 double %8.0g 18 employment employment19 double %8.0g 19 employment employment20 double %8.0g 20 employment ------------------------------------------------------------------------------- Sorted by: countyname . summarize; Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- countyname | 0 employment1 | 102 558.1667 326.5364 83 1828 employment2 | 102 93.60784 115.9182 0 533 employment3 | 102 3276.235 12304.27 89 119355 employment4 | 102 9129.039 35199.47 100 342422 -------------+-------------------------------------------------------- employment5 | 102 2186.176 9615.875 23 92706 employment6 | 102 6308.549 25002.64 167 244344 employment7 | 102 2989.588 14950.75 98 149391 employment8 | 102 463.2843 1395.181 9 13074 employment9 | 102 1692.441 8432.888 7 82835 -------------+-------------------------------------------------------- employment10 | 102 3531.51 17146.49 34 168194 employment11 | 102 999.5588 5233.395 0 51637 employment12 | 102 3728.529 20638.53 17 202319 employment13 | 102 45.2549 206.8658 0 1863 employment14 | 102 2019.48 10295.36 18 102300 -------------+-------------------------------------------------------- employment15 | 102 4899.373 19749.85 125 194308 employment16 | 102 6198.539 26209.11 233 260643 employment17 | 102 927.2255 4191.075 4 41029 employment18 | 102 3164.99 13904.79 43 138563 employment19 | 102 2704.912 12097.47 78 120337 -------------+-------------------------------------------------------- employment20 | 102 2271.627 9476.121 64 93611 . ** what is the easy way to do this? * ; . generate employment_tot = employment1 + employment2 + employment3 + employment4 + > employment5 + employment6 + employment7 + employment8 + > employment9 + employment10 + employment11 + employment12 + > employment13 + employment14 + employment15 + employment16 + > employment17 + employment18 + employment19 + employment20 ; . list in 1/1; +-------------------------------------------------------------------------------------------------------------+ 1. | county~e | emplo~t1 | emplo~t2 | emplo~t3 | emplo~t4 | emplo~t5 | emplo~t6 | emplo~t7 | emplo~t8 | emplo~t9 | | adams | 944 | 113 | 1921 | 5525 | 1485 | 4592 | 1265 | 221 | 839 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | emplo~10 | emplo~11 | emplo~12 | emplo~13 | emplo~14 | emplo~15 | emplo~16 | emplo~17 | emplo~18 | emplo~19 | | 1399 | 484 | 583 | 5 | 959 | 2782 | 4776 | 399 | 2281 | 1693 | |------------------------------------------------------+------------------------------------------------------| | emplo~20 | employ~t | | 1078 | 33344 | +-------------------------------------------------------------------------------------------------------------+ . ****************************************************************** ; . * now output these data to nice *.csv file to take a look * ; . * and compare to published figures * ; . ****************************************************************** ; . outsheet using "c:\paulafiles\teach\harris\practicum_f09\employment_IL_2000.csv", comma replace ; . *********************************************************** ; . * now compute some employment specialization indexes ! * ; . *********************************************************** ; . * compute employment shares * ; . ******************************* ; . foreach var of varlist employment1-employment20 { ; 2. generate share`var' = `var'/employment_tot; 3. format share`var' %8.3f; 4. } ; . list countyname employment_tot employment1-employment20 shareemployment1-shareemployment20 in 1/1; +-------------------------------------------------------------------------------------------------------------+ 1. | county~e | employ~t | emplo~t1 | emplo~t2 | emplo~t3 | emplo~t4 | emplo~t5 | emplo~t6 | emplo~t7 | emplo~t8 | | adams | 33344 | 944 | 113 | 1921 | 5525 | 1485 | 4592 | 1265 | 221 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | emplo~t9 | emplo~10 | emplo~11 | emplo~12 | emplo~13 | emplo~14 | emplo~15 | emplo~16 | emplo~17 | emplo~18 | | 839 | 1399 | 484 | 583 | 5 | 959 | 2782 | 4776 | 399 | 2281 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | emplo~19 | emplo~20 | share~t1 | share~t2 | share~t3 | share~t4 | share~t5 | share~t6 | share~t7 | share~t8 | | 1693 | 1078 | 0.028 | 0.003 | 0.058 | 0.166 | 0.045 | 0.138 | 0.038 | 0.007 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | share~t9 | share~10 | share~11 | share~12 | share~13 | share~14 | share~15 | share~16 | share~17 | share~18 | | 0.025 | 0.042 | 0.015 | 0.017 | 0.000 | 0.029 | 0.083 | 0.143 | 0.012 | 0.068 | |------------------------------------------------------+------------------------------------------------------| | share~19 | share~20 | | 0.051 | 0.032 | +-------------------------------------------------------------------------------------------------------------+ . /* print out records where shares do not add to 1 (they SHOULD add to 1 if done correctly) */ > list countyname if abs((shareemployment1 + shareemployment2 + shareemployment3 + > shareemployment4 + shareemployment5 + shareemployment6 + > shareemployment7 + shareemployment8 + shareemployment9 + > shareemployment10 + shareemployment11 + shareemployment12 + > shareemployment14 + shareemployment15 + shareemployment16 + > shareemployment17 + shareemployment18 + shareemployment19 + > shareemployment20) - 1) > .001; +------------+ | countyname | |------------| 12. | clark | 22. | dupage | 30. | gallatin | 33. | hamilton | 49. | lake | |------------| 55. | macon | 63. | mchenry | 70. | moultrie | 74. | piatt | 86. | shelby | |------------| 89. | stephenson | +------------+ . **************************************** ; . * compute Herfindahl for each county * ; . **************************************** ; . * WHAT IS THE EASY WAY TO DO THIS...THERE MUST BE A SHORTCUT ; . generate herf = (shareemployment1^2 + shareemployment2^2 + shareemployment3^2 + > shareemployment4^2 + shareemployment5^2 + shareemployment6^2 + > shareemployment7^2 + shareemployment8^2 + shareemployment9^2 + > shareemployment10^2 + shareemployment11^2 + shareemployment12^2 + > shareemployment14^2 + shareemployment15^2 + shareemployment16^2 + > shareemployment17^2 + shareemployment18^2 + shareemployment19^2 + > shareemployment20^2)*10000; . summarize herf , detail ; herf ------------------------------------------------------------- Percentiles Smallest 1% 786.8857 776.1995 5% 817.7767 786.8857 10% 829.9612 791.0624 Obs 102 25% 882.2568 799.2028 Sum of Wgt. 102 50% 938.4626 Mean 978.7917 Largest Std. Dev. 143.4376 75% 1027.796 1325.771 90% 1207.409 1334.836 Variance 20574.35 95% 1277.584 1370.531 Skewness 1.244407 99% 1370.531 1481.677 Kurtosis 4.199897 . *********************************************************************************** ; . * compute Krugman index, as defined on p. 15 of "Geographical Processes....", by * ; . * authors Crescenzi, Rodriguez-Pose, and Storper * ; . *********************************************************************************** ; . * for each county-industry pair, calculate share of employment in that * ; . * industry everywhere else (excluding that county) * ; . *************************************************************************** ; . /* calculate total employment in each industry */ > foreach var of varlist employment1-employment20 { ; 2. egen tot`var' = sum(`var'); 3. } ; . /* calculate total employment over all industries and counties */ > egen total_tot = sum(employment_tot); . /* calculate 'not own' share for each industry,county pair */ > foreach var of varlist employment1-employment20 { ; 2. generate othershare`var' = (tot`var' - `var')/(total_tot - employment_tot); 3. format othershare`var' %8.3f; 4. } ; . describe, fullnames; Contains data obs: 102 vars: 84 size: 43,452 (99.6% of memory free) ------------------------------------------------------------------------------- storage display value variable name type format label variable label ------------------------------------------------------------------------------- countyname str10 %10s employment1 double %8.0g 1 employment employment2 double %8.0g 2 employment employment3 double %8.0g 3 employment employment4 double %8.0g 4 employment employment5 double %8.0g 5 employment employment6 double %8.0g 6 employment employment7 double %8.0g 7 employment employment8 double %8.0g 8 employment employment9 double %8.0g 9 employment employment10 double %8.0g 10 employment employment11 double %8.0g 11 employment employment12 double %8.0g 12 employment employment13 double %8.0g 13 employment employment14 double %8.0g 14 employment employment15 double %8.0g 15 employment employment16 double %8.0g 16 employment employment17 double %8.0g 17 employment employment18 double %8.0g 18 employment employment19 double %8.0g 19 employment employment20 double %8.0g 20 employment employment_tot float %9.0g shareemployment1 float %8.3f shareemployment2 float %8.3f shareemployment3 float %8.3f shareemployment4 float %8.3f shareemployment5 float %8.3f shareemployment6 float %8.3f shareemployment7 float %8.3f shareemployment8 float %8.3f shareemployment9 float %8.3f shareemployment10 float %8.3f shareemployment11 float %8.3f shareemployment12 float %8.3f shareemployment13 float %8.3f shareemployment14 float %8.3f shareemployment15 float %8.3f shareemployment16 float %8.3f shareemployment17 float %8.3f shareemployment18 float %8.3f shareemployment19 float %8.3f shareemployment20 float %8.3f herf float %9.0g totemployment1 float %9.0g totemployment2 float %9.0g totemployment3 float %9.0g totemployment4 float %9.0g totemployment5 float %9.0g totemployment6 float %9.0g totemployment7 float %9.0g totemployment8 float %9.0g totemployment9 float %9.0g totemployment10 float %9.0g totemployment11 float %9.0g totemployment12 float %9.0g totemployment13 float %9.0g totemployment14 float %9.0g totemployment15 float %9.0g totemployment16 float %9.0g totemployment17 float %9.0g totemployment18 float %9.0g totemployment19 float %9.0g totemployment20 float %9.0g total_tot float %9.0g othershareemployment1 float %8.3f othershareemployment2 float %8.3f othershareemployment3 float %8.3f othershareemployment4 float %8.3f othershareemployment5 float %8.3f othershareemployment6 float %8.3f othershareemployment7 float %8.3f othershareemployment8 float %8.3f othershareemployment9 float %8.3f othershareemployment10 float %8.3f othershareemployment11 float %8.3f othershareemployment12 float %8.3f othershareemployment13 float %8.3f othershareemployment14 float %8.3f othershareemployment15 float %8.3f othershareemployment16 float %8.3f othershareemployment17 float %8.3f othershareemployment18 float %8.3f othershareemployment19 float %8.3f othershareemployment20 float %8.3f ------------------------------------------------------------------------------- Sorted by: countyname Note: dataset has changed since last saved . list in 1/1; +-------------------------------------------------------------------------------------------------------------+ 1. | county~e | emplo~t1 | emplo~t2 | emplo~t3 | emplo~t4 | emplo~t5 | emplo~t6 | emplo~t7 | emplo~t8 | emplo~t9 | | adams | 944 | 113 | 1921 | 5525 | 1485 | 4592 | 1265 | 221 | 839 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | emplo~10 | emplo~11 | emplo~12 | emplo~13 | emplo~14 | emplo~15 | emplo~16 | emplo~17 | emplo~18 | emplo~19 | | 1399 | 484 | 583 | 5 | 959 | 2782 | 4776 | 399 | 2281 | 1693 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | emplo~20 | employ~t | share~t1 | share~t2 | share~t3 | share~t4 | share~t5 | share~t6 | share~t7 | share~t8 | | 1078 | 33344 | 0.028 | 0.003 | 0.058 | 0.166 | 0.045 | 0.138 | 0.038 | 0.007 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | share~t9 | share~10 | share~11 | share~12 | share~13 | share~14 | share~15 | share~16 | share~17 | share~18 | | 0.025 | 0.042 | 0.015 | 0.017 | 0.000 | 0.029 | 0.083 | 0.143 | 0.012 | 0.068 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | share~19 | share~20 | herf | totem~t1 | totem~t2 | totem~t3 | totem~t4 | totem~t5 | totem~t6 | totem~t7 | | 0.051 | 0.032 | 936.8014 | 56933 | 9548 | 334176 | 931162 | 222990 | 643472 | 304938 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | totem~t8 | totem~t9 | totem~10 | totem~11 | totem~12 | totem~13 | totem~14 | totem~15 | totem~16 | totem~17 | | 47255 | 172629 | 360214 | 101955 | 380310 | 4616 | 205987 | 499736 | 632251 | 94577 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | totem~18 | totem~19 | totem~20 | total_~t | other~t1 | other~t2 | other~t3 | other~t4 | other~t5 | other~t6 | | 322829 | 275901 | 231706 | 5833185 | 0.010 | 0.002 | 0.057 | 0.160 | 0.038 | 0.110 | |----------+----------+----------+----------+----------+----------+----------+----------+----------+----------| | other~t7 | other~t8 | other~t9 | other~10 | other~11 | other~12 | other~13 | other~14 | other~15 | other~16 | | 0.052 | 0.008 | 0.030 | 0.062 | 0.017 | 0.065 | 0.001 | 0.035 | 0.086 | 0.108 | |-------------------------------------------------------------------------------------------------------------| | other~17 | other~18 | other~19 | other~20 | | 0.016 | 0.055 | 0.047 | 0.040 | +-------------------------------------------------------------------------------------------------------------+ . *************************************************************************** ; . * sum up all the absolute values of deviations of own from other shares * ; . *************************************************************************** ; . foreach var of varlist shareemployment1-shareemployment20 { ; 2. generate dev`var' = abs(`var' - other`var'); 3. } ; . generate krugman = devshareemployment1 + devshareemployment2 + > devshareemployment3 + devshareemployment4 + > devshareemployment5 + devshareemployment6 + > devshareemployment7 + devshareemployment8 + > devshareemployment9 + devshareemployment10 + > devshareemployment11 + devshareemployment12 + > devshareemployment13 + devshareemployment14 + > devshareemployment15 + devshareemployment16 + > devshareemployment17 + devshareemployment18 + > devshareemployment19 + devshareemployment20 ; . summarize krugman , detail; krugman ------------------------------------------------------------- Percentiles Smallest 1% .1247623 .1173272 5% .173336 .1247623 10% .1913919 .1589814 Obs 102 25% .2469514 .164686 Sum of Wgt. 102 50% .3159377 Mean .3154402 Largest Std. Dev. .0962317 75% .3654495 .4974299 90% .4382155 .5107238 Variance .0092605 95% .4897562 .5176743 Skewness .4911691 99% .5176743 .6457794 Kurtosis 3.438913 . ************************************************************** ; . * compare and graph herf & krugman indexes * ; . ************************************************************** ; . correlate herf krugman; (obs=102) | herf krugman -------------+------------------ herf | 1.0000 krugman | 0.3673 1.0000 . graph twoway scatter herf krugman , title("Employment Specialization Indexes, Illlinois, 2000") > saving(c:\paulafiles\teach\harris\practicum_f09\graph_il01 > .gph, replace); (note: file c:\paulafiles\teach\harris\practicum_f09\graph_il01.gph not found) (file c:\paulafiles\teach\harris\practicum_f09\graph_il01.gph saved) . graph use c:\paulafiles\teach\harris\practicum_f09\graph_il01.gph; . graph export c:\paulafiles\teach\harris\practicum_f09\graph_il01.wmf, replace; (note: file c:\paulafiles\teach\harris\practicum_f09\graph_il01.wmf not found) (file c:\paulafiles\teach\harris\practicum_f09\graph_il01.wmf written in Windows Metafile format) . clear; . log close; log: c:\paulafiles\teach\harris\practicum_f09\emp_spec_prog10.txt log type: text closed on: 21 Oct 2009, 12:03:43 ----------------------------------------------------------------------------------------------------------------------------