------------------------------------------------------------------------------- log: /home/nate/roads/CD-ROM/programs/form500-cons.log log type: text opened on: 14 Aug 2005, 08:56:53 . . set mem 40m (40960k) . . . ************** 1. Read in Raw Data ********************* . . *read in route log and finder list data . insheet using ../data/routelog.csv (9 vars, 484 obs) . compress . . sort statefips intst . save ../data/routelog.dta,replace file ../data/routelog.dta saved . clear . . *read in the form 500 data . . #delimit ; delimiter now ; . infile sh i str5 rte seo st mpost seg str5 check str1 c str4 stgp date1 date2 > > date3 date4 date5 date6 date7 date8 date9 using ../data/roads2_final.csv; (eof not at end of obs) (7847 observations read) . gen newrd = 0; . compress; sh was float now byte i was float now byte seo was float now byte st was float now byte seg was float now int date1 was float now int date2 was float now int date3 was float now int date4 was float now int date5 was float now int date6 was float now int date7 was float now int date8 was float now int date9 was float now int newrd was float now byte rte was str5 now str4 . save ../data/roads2_final.dta,replace; (note: file ../data/roads2_final.dta not found) file ../data/roads2_final.dta saved . clear; . infile sh i str5 rte seo st mpost seg str5 check str1 c str4 stgp date1 date2 > date3 date4 date5 date6 date7 date8 date9 using ../data/roads1_final.csv; (7835 observations read) . gen newrd = 0; . save ../data/roads1_final.dta,replace; (note: file ../data/roads1_final.dta not found) file ../data/roads1_final.dta saved . clear; . infile sh i str5 rte seo st mpost seg str5 check str1 c str4 stgp date1 date2 > date3 date4 date5 date6 date7 date8 date9 using ../data/roads3_final.csv; (eof not at end of obs) (568 observations read) . gen newrd = 1; . append using ../data/roads2_final.dta; . append using ../data/roads1_final.dta; . #delimit cr delimiter now cr . . sort st rte . save ../data/roads_final.dta,replace file ../data/roads_final.dta saved . . erase ../data/roads2_final.dta . erase ../data/roads1_final.dta . . . ************** 2. Clean Up Variables ************************** . . *These are completely empty observations . sum if st==. Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- sh | 0 i | 0 rte | 0 seo | 0 st | 0 -------------+-------------------------------------------------------- mpost | 0 seg | 0 check | 0 c | 0 stgp | 0 -------------+-------------------------------------------------------- date1 | 0 date2 | 0 date3 | 0 date4 | 0 date5 | 0 -------------+-------------------------------------------------------- date6 | 0 date7 | 0 date8 | 0 date9 | 0 newrd | 19 .3684211 .4955946 0 1 . tab rte if st==. no observations . drop if st==. (19 observations deleted) . . keep rte st mpost seg check stgp date* newrd . . *fix up intst . replace rte = trim(rte) (0 real changes made) . gen RTE = real(rte) (164 missing values generated) . replace RTE = RTE/10 (16067 real changes made) . replace rte = "35E" if rte=="035E" (86 real changes made) . replace rte = "35W" if rte=="035W" (76 real changes made) . replace rte = "35W" if rte=="03SW" (2 real changes made) . replace rte = string(RTE) if rte~="35E" & rte~="35W" (16067 real changes made) . gen str3 intst = trim(rte) . . *fix mpost flag variable . replace check = "0" if trim(check)=="ERROR" (1363 real changes made) . gen chck = real(check) (7 missing values generated) . drop check . rename chck check . . *make dates reals in the year only . gen d_stgp = date1-100*int(date1/100) (13 missing values generated) . gen d_3to1 = date2-100*int(date2/100) (3182 missing values generated) . gen d_2to1 = date3-100*int(date3/100) (13942 missing values generated) . gen d_3to2 = date4-100*int(date4/100) (13957 missing values generated) . gen d_4to3 = date5-100*int(date5/100) (7790 missing values generated) . gen d_4 = date6-100*int(date6/100) (10748 missing values generated) . gen d_5to43 = date7-100*int(date7/100) (13031 missing values generated) . gen d_5to41 = date8-100*int(date8/100) (15822 missing values generated) . gen d_6 = date9-100*int(date9/100) (15633 missing values generated) . . *change variable names to match other dataset . rename st statefips . rename seg length . . *drop Hawaii/Alaska . drop if statefips==15|statefips==2 (85 observations deleted) . . *fix stgp . tab stgp stgp | Freq. Percent Cum. ------------+----------------------------------- 1A1F | 14,630 90.61 90.61 1A2F | 118 0.73 91.34 1A3F | 136 0.84 92.18 1AIF | 1 0.01 92.19 1B1F | 278 1.72 93.91 1B2F | 1 0.01 93.92 1B3F | 35 0.22 94.13 1H1F | 163 1.01 95.14 1H2F | 1 0.01 95.15 2A1F | 31 0.19 95.34 2A1P | 1 0.01 95.35 2A2F | 69 0.43 95.78 2A3F | 8 0.05 95.83 2B1F | 1 0.01 95.83 2B2F | 7 0.04 95.88 2H1F | 6 0.04 95.91 2H2F | 1 0.01 95.92 2H3F | 2 0.01 95.93 3A1F | 8 0.05 95.98 3A2 | 47 0.29 96.27 3A3 | 8 0.05 96.32 3H1F | 6 0.04 96.36 3H2 | 18 0.11 96.47 4A1 | 5 0.03 96.50 5A2 | 1 0.01 96.51 6B1 | 542 3.36 99.86 6B2 | 1 0.01 99.87 6B3 | 1 0.01 99.88 6BI | 15 0.09 99.97 BA2 | 1 0.01 99.98 IA3F | 2 0.01 99.99 IAIF | 2 0.01 100.00 ------------+----------------------------------- Total | 16,146 100.00 . replace stgp = "2A1F" if stgp=="2A1P" (1 real change made) . replace stgp = "1A1F" if stgp=="1AIF" (1 real change made) . replace stgp = "6B1" if stgp=="6BI" (15 real changes made) . replace stgp = "1A3F" if stgp=="IA3F" (2 real changes made) . replace stgp = "1A1F" if stgp=="IAIF" (2 real changes made) . replace stgp = "3A2" if stgp=="BA2" (1 real change made) . . . ***************** 3. Fix Up Milepost Numbers ********************** . . /*Fix the mposts on roads to match gisdat.dta. In the form500 data, > it sets 35E and 35W in Texas and Minnesota with mposts as continuations > of 35. 35 officially continues along with 35E in both cases. Similar situat > ions > occur for other highways that overlap. Also 3-digit highways are numbered > according to their milepost of the master 2-digit highway and are > converted to a milepost system commensurate with gisdat.dta.*/ . . sort statefips intst mpost . . **These are local interstates that start at nonzero numbers . gen x = 0 . by statefips intst: replace x = mpost[1] if real(intst)>100 & real(intst)~=. (1376 real changes made) . replace mpost=mpost-x (1376 real changes made) . drop x . . /** These are gaps due to dual-numbered highways: > Each of these lines of code come from investigation of > each highway separately in the data **/ . replace mpost=mpost-8827 if statefips==27 & intst=="35E" (34 real changes made) . replace mpost=mpost-37051 if statefips==48 & intst=="35W" (36 real changes made) . replace mpost=mpost-37051 if statefips==48 & intst=="35E" (52 real changes made) . replace mpost=mpost-30121 if statefips==48 & intst=="27" (46 real changes made) . * fix 1 20 that has 130 miles of overlap at the beginning . replace mpost = mpost+13010 if statefips==1 & intst=="20" (39 real changes made) . * 6 80 is listed as starting at 530 and is too long so removing the 530 . replace mpost = mpost-530 if statefips==6 & intst=="80" (107 real changes made) . * fix 18 465: roads_final3 mposts are inconsistent with 1 and 2 . replace mpost = mpost-10139 if mpost>503 & statefips==18 & intst=="465" (30 real changes made) . replace mpost = mpost-557 if mpost>=14482 & statefips==18 & intst=="465" (0 real changes made) . * fix 20 435 No evidence of this portion found on map, but in f 500 data . replace mpost = mpost-5546 if mpost>=7580 & statefips==20 & intst=="435" (5 real changes made) . * fix 27 494 . replace mpost = mpost-3016 if mpost>2800 & statefips==27 & intst=="494" (19 real changes made) . * fix 28 20/55 . replace mpost = mpost+2520 if mpost>4400 & statefips==28 & intst=="20" (16 real changes made) . * fix 29 29/35 . replace mpost = mpost+550 if statefips==29 & intst=="29" (28 real changes made) . * fix 29 64 first segment of highway (mi 0-11) never built . replace mpost = mpost-1110 if statefips==29 & intst=="64" (1 real change made) . * fix 34 76 . replace mpost = mpost-2800 if statefips==34 & intst=="76" (4 real changes made) . * fix 39 70: if make this fix then matches up to observed length . replace mpost = mpost-609 if mpost> 10121 & statefips==39 & intst=="70" (47 real changes made) . * fix 39 271/480 . replace mpost = mpost+584 if mpost>2070 & statefips==39 & intst=="271" (7 real changes made) . * fix 39 275/74 . replace mpost = mpost+345 if mpost>1000 & statefips==39 & intst=="275" (34 real changes made) . * fix 39 90 for 143 mi of overlap at the beginning . replace mpost = mpost+14280 if statefips==39 & intst=="90" (51 real changes made) . * fix 41 84: 3 mi too long in form 500 and 3 mi gap, so erase gap . replace mpost = mpost-232 if mpost>=1007 & statefips==41 & intst=="84" (107 real changes made) . * fix 42 79: matches if take out this part for which no form500 data . replace mpost = mpost-555 if mpost>6769 & statefips==42 & intst=="79" (47 real changes made) . * fix 45 526 . replace mpost = mpost+898 if statefips==45 & intst=="526" & stgp=="1B1F" (2 real changes made) . * fix 47 65/40 . replace mpost = mpost+100 if mpost>8250 & statefips==47 & intst=="65" (11 real changes made) . * fix 48 44 . replace mpost = mpost-20000 if statefips==48 & intst=="44" (6 real changes made) . * fix 49 80/15 . replace mpost = mpost+331 if mpost>12050 & statefips==49 & intst=="80" (18 real changes made) . * fix 49 84, insert record (which is duplicate of 15) from 41 to 77 . replace mpost = mpost+3598 if mpost>4100 & statefips==49 & intst=="84" (15 real changes made) . * fix 47 75, insert record (which duplicates I 40) from 84 to 104 . replace mpost = mpost+1960 if mpost>8400 & statefips==47 & intst=="75" (15 real changes made) . * 51 64 insert for duplicate of 81 . replace mpost = mpost+3040 if mpost>5600 & statefips==51 & intst=="64" (60 real changes made) . * 51 77 insert for duplicate of 81 . replace mpost = mpost+880 if mpost>3200 & statefips==51 & intst=="77" (13 real changes made) . * 42 70 insert for duplicate of 76 (calculated with Arcview and printed guide > ) . replace mpost = mpost+8722 if mpost>5864 & statefips==42 & intst=="70" (6 real changes made) . . . *************** 4. Flag and Fix Repeated Segments ************************* . . sort statefips intst mpost . *check0 flags problem obs off by 1 mi or more from next obs . *check1 flags problem obs off by any amount from next obs . gen check0 = 0 . gen check1 = 0 . qui by statefips intst: replace check0 = 1 if abs(mpost[_n+1]-mpost-length)<1 > 00 . qui by statefips intst: replace check1 = 1 if mpost[_n+1]==mpost+length . . /*drop repeated segments (with improvements at different dates) > do it several times to delete several segments in a row > The routine below flags all overlapping observations and then > deletes the second one covering the same mileage*/ . gen flag = 0 . local i = 1 . while `i'<=_N { 2. *disp `i' . if check0[`i']==0 & intst[`i']==intst[`i'+1] { 3. local ref = mpost[`i']+length[`i'] 4. local i = `i'+1 5. if `ref'>mpost[`i']-50 { 6. if abs(mpost[`i'+1]-`ref')100 (64 real changes made) . by statefips intst: replace gap = 0 if _n==1 (4 real changes made) . by statefips intst: replace gap = 1 if mpost~=0 & _n==1 (11 real changes made) . list statefips intst mpost if gap==1 +--------------------------+ | statef~s intst mpost | |--------------------------| 27. | 1 20 13010 | 988. | 6 15 610 | 1977. | 8 25 17236 | 3332. | 13 85 8444 | 3489. | 16 84 12196 | |--------------------------| 3494. | 16 84 14120 | 3752. | 17 39 8000 | 3780. | 17 55 1870 | 4044. | 17 57 16324 | 4204. | 17 64 7538 | |--------------------------| 4433. | 17 74 13338 | 4664. | 17 90 10039 | 4765. | 18 465 4343 | 5029. | 18 70 8299 | 5077. | 18 74 9392 | |--------------------------| 5130. | 18 94 1605 | 5175. | 19 29 5020 | 5195. | 19 29 14016 | 5240. | 19 35 17280 | 5270. | 19 680 1278 | |--------------------------| 5308. | 19 80 13779 | 5835. | 21 75 11740 | 6947. | 26 69 8210 | 7293. | 27 35 12750 | 7684. | 28 20 6960 | |--------------------------| 7849. | 29 29 550 | 7918. | 29 435 1560 | 8265. | 30 15 16619 | 8362. | 30 90 22603 | 8874. | 34 287 540 | |--------------------------| 8879. | 34 287 2200 | 8914. | 34 295 2800 | 8957. | 34 78 600 | 8972. | 34 78 3500 | 8994. | 34 78 6700 | |--------------------------| 9436. | 36 287 1975 | 9515. | 36 495 780 | 9539. | 36 684 600 | 10008. | 37 40 25939 | 10251. | 38 29 11040 | |--------------------------| 10416. | 39 271 2755 | 10426. | 39 275 1441 | 10698. | 39 71 22 | 10748. | 39 71 10788 | 10981. | 39 76 5430 | |--------------------------| 11121. | 39 90 14280 | 11349. | 40 40 15335 | 11423. | 40 44 13467 | 11644. | 41 84 29 | 11899. | 42 70 14663 | |--------------------------| 12407. | 42 84 579 | 12690. | 45 526 1796 | 12972. | 47 24 5000 | 13029. | 47 40 1780 | 13033. | 47 40 5180 | |--------------------------| 13167. | 47 65 8370 | 13203. | 47 75 10375 | 13406. | 48 10 57275 | 14034. | 48 35 46746 | 14267. | 48 410 4248 | |--------------------------| 14474. | 49 15 7139 | 14479. | 49 15 10590 | 14546. | 49 15 32910 | 14675. | 49 80 12781 | 14700. | 49 84 8106 | |--------------------------| 14898. | 51 64 8752 | 15013. | 51 77 4109 | 15143. | 51 95 7919 | 15231. | 53 5 3 | 15677. | 54 64 11982 | |--------------------------| 15873. | 55 94 23876 | +--------------------------+ . . label variable gap "observation that is a gap in the form 500 data" . . *create new observations to fill in missing ones . gen mpostm1 = mpost[_n-1] (1 missing value generated) . gen lengthm1 = length[_n-1] (1 missing value generated) . by statefips intst: replace mpostm1 = 0 if _n==1 (454 real changes made) . by statefips intst: replace lengthm1 = 0 if _n==1 (474 real changes made) . save temp.dta,replace file temp.dta saved . keep if gap==1 (16039 observations deleted) . *length is difference between beg of this seg and end of last seg . replace length = mpost-(mpostm1+lengthm1) (71 real changes made) . *mpost is end of last segment . replace mpost = mpostm1+lengthm1 (71 real changes made) . replace check0 = 1 (1 real change made) . replace check1 = 1 (9 real changes made) . replace stgp = "" (71 real changes made) . replace d_stgp = . (71 real changes made, 71 to missing) . replace d_3to1 = . (57 real changes made, 57 to missing) . replace d_2to1 = . (9 real changes made, 9 to missing) . replace d_3to2 = . (9 real changes made, 9 to missing) . replace d_4to3 = . (31 real changes made, 31 to missing) . replace d_4 = . (21 real changes made, 21 to missing) . replace d_5to43 = . (12 real changes made, 12 to missing) . replace d_5to41 = . (1 real change made, 1 to missing) . replace d_6 = . (4 real changes made, 4 to missing) . . *append back on -- missing values will be created for date variables . append using temp.dta . drop mpostm1 lengthm1 . sort statefips intst mpost . . *make mposts for ends of segment . gen mpost2 = . (16181 missing values generated) . replace mpost2 = mpost+length (16181 real changes made) . . *put mposts in whole miles so that gisdat can be merged on . replace mpost = mpost/100 (15706 real changes made) . replace mpost2 = mpost2/100 (16181 real changes made) . replace length = length/100 (16181 real changes made) . . . ************************ 6a. Check Data *************************** . . *do data checks . list statefips intst mpost d_3to2 d_2to1 if d_3to2>d_2to1 & d_3to2~=. +---------------------------------------------+ | statef~s intst mpost d_3to2 d_2to1 | |---------------------------------------------| 8216. | 29 70 209.8 74 71 | 8545. | 31 480 2.65 70 69 | 9229. | 35 25 310.32 66 60 | +---------------------------------------------+ . . list statefips intst mpost d_stgp if (d_stgp>92 | d_stgp<40) & d_stgp~=. +------------------------------------+ | statef~s intst mpost d_stgp | |------------------------------------| 18. | 1 165 0 93 | 19. | 1 165 .4 93 | 20. | 1 165 .8 93 | 21. | 1 165 1.36 93 | 22. | 1 165 2.05 93 | |------------------------------------| 23. | 1 165 2.85 93 | 24. | 1 165 2.9 93 | 25. | 1 165 4.05 93 | 26. | 1 165 4.35 93 | 973. | 6 105 13.6 93 | |------------------------------------| 974. | 6 105 15 93 | 982. | 6 110 11 93 | 1757. | 6 80 0 36 | 1758. | 6 80 3.4 36 | 1759. | 6 80 4.7 36 | |------------------------------------| 2447. | 9 95 76.73 93 | 2448. | 9 95 78.06 93 | 2449. | 9 95 78.75 93 | 3163. | 13 285 0 93 | 3164. | 13 285 9.48 93 | |------------------------------------| 3165. | 13 285 12.04 93 | 3166. | 13 285 13.73 93 | 3181. | 13 285 61.4 93 | 3197. | 13 575 0 93 | 3198. | 13 575 1.7 93 | |------------------------------------| 3199. | 13 575 5.96 93 | 3200. | 13 575 7.12 93 | 3201. | 13 575 7.27 93 | 3202. | 13 575 16.53 93 | 3203. | 13 575 19.76 93 | |------------------------------------| 6109. | 22 220 0 93 | 6110. | 22 220 1.18 93 | 6111. | 22 220 1.5 93 | 6112. | 22 220 3.1 93 | 6113. | 22 220 5 93 | |------------------------------------| 6114. | 22 220 8.3 93 | 6115. | 22 220 8.5 93 | 6116. | 22 220 8.78 93 | 6117. | 22 220 9 93 | 6118. | 22 220 11 93 | |------------------------------------| 6119. | 22 220 12 93 | 6120. | 22 220 12.4 93 | 6121. | 22 220 14.68 93 | 6122. | 22 220 15.4 93 | 6123. | 22 220 16.1 93 | |------------------------------------| 6124. | 22 310 0 93 | 6125. | 22 310 .39 93 | 6126. | 22 310 2.15 93 | 6127. | 22 310 3.16 93 | 6128. | 22 310 3.59 93 | |------------------------------------| 6129. | 22 310 5.16 93 | 6130. | 22 310 5.91 93 | 6131. | 22 310 6.22 93 | 6132. | 22 310 6.91 93 | 6133. | 22 310 7.42 93 | |------------------------------------| 6134. | 22 310 8.05 93 | 6135. | 22 310 9.18 93 | 6144. | 22 49 23.32 93 | 6145. | 22 49 25.55 93 | 6146. | 22 49 26.82 93 | |------------------------------------| 6147. | 22 49 28.37 93 | 6148. | 22 49 34.71 93 | 6149. | 22 49 38.69 93 | 6150. | 22 49 39.15 93 | 6151. | 22 49 40.27 93 | |------------------------------------| 6152. | 22 49 42.5 93 | 6153. | 22 49 42.96 93 | 6154. | 22 49 45.55 93 | 6155. | 22 49 46.88 93 | 6157. | 22 49 53.9 93 | |------------------------------------| 6158. | 22 49 54.26 93 | 6159. | 22 49 54.8 93 | 6160. | 22 49 56.97 93 | 6161. | 22 49 61.8 93 | 6162. | 22 49 67.24 93 | |------------------------------------| 6163. | 22 49 74.01 93 | 6164. | 22 49 78.83 93 | 6165. | 22 49 79.97 93 | 6179. | 22 49 86.55 93 | 6180. | 22 49 87.07 93 | |------------------------------------| 6181. | 22 49 89.88 93 | 6182. | 22 49 91.21 93 | 6183. | 22 49 91.42 93 | 6184. | 22 49 91.71 93 | 6185. | 22 49 96.11 93 | |------------------------------------| 6186. | 22 49 99.11 93 | 6187. | 22 49 102.04 93 | 6214. | 22 49 201.19 93 | 6225. | 22 510 .16 93 | 8345. | 30 90 10.7 93 | |------------------------------------| 9600. | 36 78 0 27 | 10138. | 37 77 99.83 93 | 10139. | 37 77 100.83 93 | 11295. | 40 35 121.77 93 | 11880. | 42 476 1.9 93 | |------------------------------------| 11881. | 42 476 4.96 93 | 11882. | 42 476 5.2 93 | 11883. | 42 476 8.1 93 | 11884. | 42 476 9 93 | 11885. | 42 476 10.2 93 | |------------------------------------| 11886. | 42 476 10.75 93 | 11887. | 42 476 11.88 93 | 11888. | 42 476 12.6 93 | 11889. | 42 476 12.9 93 | 11890. | 42 476 13.4 93 | |------------------------------------| 11891. | 42 476 14.2 93 | 11892. | 42 476 14.31 93 | 11893. | 42 476 14.71 93 | 11894. | 42 476 15.5 93 | 11900. | 42 476 19.8 93 | |------------------------------------| 11901. | 42 476 20.4 93 | 12510. | 42 95 0 93 | 12511. | 42 95 2.32 93 | 12512. | 42 95 4.49 93 | 12513. | 42 95 6.33 93 | |------------------------------------| 12514. | 42 95 7.52 93 | 12515. | 42 95 8.33 93 | 12516. | 42 95 8.83 93 | 12517. | 42 95 9.45 93 | 12518. | 42 95 10.03 93 | |------------------------------------| 12519. | 42 95 10.23 93 | 12539. | 42 95 20.63 93 | 12541. | 42 95 21.43 93 | 12554. | 42 95 26.69 93 | 12567. | 42 95 40.63 93 | |------------------------------------| 12568. | 42 95 40.9 93 | 12569. | 42 95 41.5 93 | 12594. | 44 295 17.53 93 | 12779. | 45 85 40.6 93 | 15289. | 53 405 16.2 93 | |------------------------------------| 15290. | 53 405 16.9 93 | 15291. | 53 405 17.8 93 | 15292. | 53 405 18 93 | 15293. | 53 405 18.1 93 | 15294. | 53 405 20.96 93 | |------------------------------------| 15295. | 53 405 21.57 93 | 15550. | 53 90 .61 93 | 15551. | 53 90 1.3 93 | 15552. | 53 90 1.57 93 | 15553. | 53 90 1.59 93 | |------------------------------------| 15554. | 53 90 3.25 93 | 15555. | 53 90 5.87 93 | +------------------------------------+ . list statefips intst mpost d_2to1 if (d_2to1>92 | d_2to1<40) & d_2to1~=. +-----------------------------------+ | statef~s intst mpost d_2to1 | |-----------------------------------| 2447. | 9 95 76.73 93 | 2448. | 9 95 78.06 93 | 2449. | 9 95 78.75 93 | +-----------------------------------+ . list statefips intst mpost d_3to1 if (d_3to1>92 | d_3to1<40) & d_3to1~=. +------------------------------------+ | statef~s intst mpost d_3to1 | |------------------------------------| 6124. | 22 310 0 93 | 6125. | 22 310 .39 93 | 6126. | 22 310 2.15 93 | 6187. | 22 49 102.04 93 | 6214. | 22 49 201.19 93 | |------------------------------------| 6225. | 22 510 .16 93 | 9460. | 36 278 24.45 36 | +------------------------------------+ . list statefips intst mpost d_3to2 if (d_3to2>92 | d_3to2<40) & d_3to2~=. +-----------------------------------+ | statef~s intst mpost d_3to2 | |-----------------------------------| 9572. | 36 678 13.5 39 | +-----------------------------------+ . list statefips intst mpost d_4to3 if (d_4to3>92 | d_4to3<40) & d_4to3~=. +-----------------------------------+ | statef~s intst mpost d_4to3 | |-----------------------------------| 21. | 1 165 1.36 93 | +-----------------------------------+ . list statefips intst mpost d_4 if (d_4>92 | d_4<40) & d_4~=. . list statefips intst mpost d_5to43 if (d_5to43>92 | d_5to43<40) & d_5to43~=. . list statefips intst mpost d_5to41 if (d_5to41>92 | d_5to41<40) & d_5to41~=. . list statefips intst mpost d_6 if (d_6>92 | d_6<40) & d_6~=. +--------------------------------+ | statef~s intst mpost d_6 | |--------------------------------| 1757. | 6 80 0 36 | 1758. | 6 80 3.4 36 | 1759. | 6 80 4.7 36 | +--------------------------------+ . . list statefips intst mpost date1 if (date1>1292 | date1<136) & date1~=. +----------------------------------+ | statef~s intst mpost date1 | |----------------------------------| 9600. | 36 78 0 127 | +----------------------------------+ . list statefips intst mpost date2 if (date2>1292 | date2<136) & date2~=. . list statefips intst mpost date3 if (date3>1292 | date3<136) & date3~=. . list statefips intst mpost date4 if (date4>1292 | date4<136) & date4~=. . list statefips intst mpost date5 if (date5>1292 | date5<136) & date5~=. . list statefips intst mpost date6 if (date6>1292 | date6<136) & date6~=. . list statefips intst mpost date7 if (date7>1292 | date7<136) & date7~=. . list statefips intst mpost date8 if (date8>1292 | date8<136) & date8~=. . list statefips intst mpost date9 if (date9>1292 | date9<136) & date9~=. . . list statefips intst mpost length if length==. . list statefips intst mpost length if mpost==. . . rename mpost mpostb . rename mpost2 mpost . . label variable mpost "mile post at end of segment" . label variable mpostb "mile post at beginning of segment before broken up" . . keep statefips intst mpost mpostb length d_* stgp gap newrd . . sort statefips intst mpost . . compress statefips was float now byte newrd was float now byte d_stgp was float now byte d_3to1 was float now byte d_2to1 was float now byte d_3to2 was float now byte d_4to3 was float now byte d_4 was float now byte d_5to43 was float now byte d_5to41 was float now byte d_6 was float now byte gap was float now byte . save tempx.dta,replace file tempx.dta saved . . . ************* 6b. Compare Lengths With Route Log & Finder List ************** > * . . **check for discrepancies with route log and list them . by statefips intst: keep if _n==_N (15707 observations deleted) . . keep statefips intst mpost length . sort statefips intst . . merge statefips intst using ../data/routelog.dta . *should be 2 or 3 ONLY except for 45/326 (see note above) . tab _merge _merge | Freq. Percent Cum. ------------+----------------------------------- 1 | 1 0.21 0.21 2 | 11 2.27 2.47 3 | 473 97.53 100.00 ------------+----------------------------------- Total | 485 100.00 . list if _merge==1 +---------------------------------------------------------------+ 386. | statef~s | length | intst | mpost | statenm | totmi | overlap | | 45 | 1.98 | 326 | 5.26 | | . | . | |---------------------------+-----------------------------------| | overrt1 | overrt2 | sec103 | nhs | _merge | | | . | . | . | 1 | +---------------------------------------------------------------+ . . gen mi500 = totmi-nhs (1 missing value generated) . . **** Each of these lines is from a manual check against the GIS data . . *13 285/85 overlap is OK b/c at end . drop if statefips==13 & intst=="285" (1 observation deleted) . *17 280/74 overlap is OK b/c at end . drop if statefips==17 & intst=="280" (1 observation deleted) . *17 294/80 overlap is OK b/c at end . drop if statefips==17 & intst=="294" (1 observation deleted) . *17 94/80 discrepancy is wrong b/c number in routelog for 94 is wrong . drop if statefips==17 & intst=="94" (1 observation deleted) . *18 90/80 overlap OK b/c at end . drop if statefips==18 & intst=="90" (1 observation deleted) . *19 80/35 mpost 3 mi too long - form 500 data is wrong . *21 71/75 overlap OK b/c at end . drop if statefips==21 & intst=="71" (1 observation deleted) . *29 55/44 at end so OK . drop if statefips==29 & intst=="55" (1 observation deleted) . *39 271/480 is too long in routelog . drop if statefips==39 & intst=="271" (1 observation deleted) . *54 64/77 too long in routelog . drop if statefips==54 & intst=="64" (1 observation deleted) . *55 94/90 is 7 too long in routelog . drop if statefips==55 & intst=="94" (1 observation deleted) . . *6 15 no explanation but small . drop if statefips==6 & intst=="15" (1 observation deleted) . **6 215 is a big discrepancy -- Reason to Use only Fed Funded Rds . **6 305 is a small discrepancy -- hard to find so don't worry about it . drop if statefips==6 & intst=="305" (1 observation deleted) . **6 80 is a small discrepancy -- may be because of uncertainty where it begin > s in SF . drop if statefips==6 & intst=="80" (1 observation deleted) . **6 880 is a small discrepancy . drop if statefips==6 & intst=="880" (1 observation deleted) . ** 13 75 is too short in form 500 data . ** 17 39 is too short in route log . drop if statefips==17 & intst=="39" (1 observation deleted) . ** 17 55 is too long in route log . drop if statefips==17 & intst=="55" (1 observation deleted) . ** 17 57 is too long in route log . drop if statefips==17 & intst=="57" (1 observation deleted) . ** 17 70 is too short in route log . drop if statefips==17 & intst=="39" (0 observations deleted) . ** 17 90 is too long in route log . drop if statefips==17 & intst=="90" (1 observation deleted) . ** 17 72 I don't observe the sec 103 miles in route log . ** 20 435 too long in f 500 data . * 21 64 too short in f 500 data . drop if statefips==21 & intst=="64" (1 observation deleted) . * 24 895 discrepancy is from spur I think . drop if statefips==24 & intst=="895" (1 observation deleted) . * 30 15 form 500 data is too short but no msas near the end so OK . ** 32 515 don't observe Sec103 miles in form 500 data . ** 45 77 has unobserved sec103 miles in form 500 data . ** 51 264 has unobserved sec103 miles in form 500 data . ** 51 664 has unobserved sec103 miles in form 500 data . ** 55 43 has unobserved sc103 miles in form 500 data . ** 894 discrepancy not explained (hard to see in mapquest) . ** 55 90 doesn't count overlap with 39 in routelog . ** 17 155 b/c of sec103 . ** as are all other . except . ** 55 39 is either sec103 (not observed) or observed as 55 90 . . * 36 495 discrepancy comes because of partially unobserved sec103 miles . * 36 990 discrepancy from sec103 miles . * 39 270 form 500 data is too short inexplicably . * 39 70 too long in form 500 data . * 42 476 discrepancy due to unobserved sec103 miles . * 45 326 was renamed to 45 77 -- I throw this segment out b/c I don't know if > relisted in 45/77 . . list statefips intst mpost totmi nhs sec103 overlap overrt1 overrt2 if abs(mi > 500-mpost)>2 & overlap~=. +-----------------------------------------------------------------------+ 125. | statef~s | intst | mpost | totmi | nhs | sec103 | overlap | overrt1 | | 19 | 29 | 150.5 | 154.75 | 0 | 0 | 3 | 80 | |-----------------------------------------------------------------------| | overrt2 | | . | +-----------------------------------------------------------------------+ +-----------------------------------------------------------------------+ 131. | statef~s | intst | mpost | totmi | nhs | sec103 | overlap | overrt1 | | 19 | 80 | 306.69 | 303.23 | 0 | 0 | 14.29 | 35 | |-----------------------------------------------------------------------| | overrt2 | | . | +-----------------------------------------------------------------------+ +-----------------------------------------------------------------------+ 354. | statef~s | intst | mpost | totmi | nhs | sec103 | overlap | overrt1 | | 42 | 79 | 179.74 | 182.72 | 0 | 0 | 3.55 | 70 | |-----------------------------------------------------------------------| | overrt2 | | . | +-----------------------------------------------------------------------+ . list statefips intst mpost totmi nhs sec103 if abs(mi500-mpost)>2 & overlap== > . +----------------------------------------------------+ | statef~s intst mpost totmi nhs sec103 | |----------------------------------------------------| 29. | 6 215 25.1 54.5 0 54.5 | 88. | 13 75 351.73 355.11 0 0 | 106. | 17 70 160.25 135.94 0 0 | 107. | 17 72 78.66 172.13 0 94 | 114. | 18 465 64.74 57.51 0 5.03 | |----------------------------------------------------| 192. | 25 95 89.63 91.95 0 37.54 | 201. | 26 69 202.2 198.51 0 66.8 | 228. | 29 435 55.5 52.78 0 0 | 237. | 30 15 387.18 396.03 0 0 | 248. | 32 515 6 20.54 0 14.9 | |----------------------------------------------------| 259. | 34 287 64 67.54 0 0 | 260. | 34 295 78.2 68.06 0 0 | 265. | 34 95 67.2 86.73 0 0 | 278. | 36 495 11.4 66.38 0 62.7 | 296. | 36 990 4.14 6.35 0 2.04 | |----------------------------------------------------| 308. | 39 270 34.25 54.97 0 0 | 324. | 39 76 78.16 81.65 0 0 | 325. | 39 77 163.04 160.13 0 0 | 343. | 42 276 29.7 32.65 0 0 | 347. | 42 380 27.92 24.76 0 0 | |----------------------------------------------------| 348. | 42 476 21.31 129.61 0 110 | 368. | 45 326 5.26 . . . | 370. | 45 526 21.56 19.26 0 11.2 | 372. | 45 77 75.17 91.05 0 9.5 | 385. | 47 40 451.5 455.28 0 0 | |----------------------------------------------------| 403. | 48 410 52.41 49.49 0 9.93 | 411. | 49 70 229.34 232.15 0 0 | 419. | 51 264 13.17 25.07 0 12 | 429. | 51 664 10.77 20.21 0 9.2 | 446. | 55 43 119.58 191.55 0 63 | |----------------------------------------------------| 449. | 55 894 10.02 4.7 0 0 | 450. | 55 90 187.17 108.61 0 0 | 455. | 17 155 . 32.13 0 32.13 | 456. | 17 172 . 19.69 0 19.69 | 457. | 18 469 . 30.83 0 30.83 | |----------------------------------------------------| 458. | 37 485 . 16.7 0 16.7 | 459. | 37 540 . 4.35 0 4.35 | 460. | 37 73 . 56.7 56.7 0 | 461. | 42 99 . 53 53 0 | 462. | 47 140 . 11.17 0 11.17 | |----------------------------------------------------| 463. | 47 181 . 23.85 0 23.85 | 464. | 54 68 . 32.06 0 32.06 | 465. | 55 39 . 182.72 0 104 | +----------------------------------------------------+ . . . *********** 7. Break Up Dataset into 1 Observation Per Milepost ************* > ***** . . use tempx.dta,clear . **break up data into milepost by milepost . run breakup.do . log off log: /home/nate/roads/CD-ROM/programs/form500-cons.log log type: text paused on: 14 Aug 2005, 08:56:57 ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- log: /home/nate/roads/CD-ROM/programs/form500-cons.log log type: text resumed on: 14 Aug 2005, 09:38:03 . . **load newly created dataset and build a few more variables . use ../data/form500,clear . . . ********* 8. Clean Up the Resulting Data Set ******************************** > * . . *fill in missing data for each segment that has been mileposted . sort statefips intst order . qui by statefips intst order: replace gap=gap[1] . qui by statefips intst order: replace d_stgp=d_stgp[1] . qui by statefips intst order: replace d_3to1=d_3to1[1] . qui by statefips intst order: replace d_4to3=d_4to3[1] . qui by statefips intst order: replace d_5to41=d_5to41[1] . qui by statefips intst order: replace mpostb=mpostb[1] . qui by statefips intst order: replace d_2to1=d_2to1[1] . qui by statefips intst order: replace d_4=d_4[1] . qui by statefips intst order: replace stgp=stgp[1] . qui by statefips intst order: replace d_3to2=d_3to2[1] . qui by statefips intst order: replace d_5to43=d_5to43[1] . qui by statefips intst order: replace d_6=d_6[1] . qui by statefips intst order: replace newrd=newrd[1] . replace length = 1 if length==. (0 real changes made) . . rename length seg . drop order . . *create local/federal funding dummy . *local is "B" and federal is "A", Howard-Cramer is "H" . gen fedfund = . (61177 missing values generated) . replace fedfund = 1 if substr(stgp,2,1)=="A" (54794 real changes made) . replace fedfund = 0 if substr(stgp,1,1)=="6" (3120 real changes made) . replace fedfund = 0 if substr(stgp,2,1)=="B" (1427 real changes made) . replace fedfund = 2 if substr(stgp,2,1)=="H" (592 real changes made) . . *year of status group 1 or 2 opening . gen d_open=min(d_3to1,d_2to1,d_3to2) (5314 missing values generated) . replace d_open=max(d_4to3,d_5to43,d_5to41) if d_open==. (427 real changes made) . replace d_open=d_6 if d_open==. (2979 real changes made) . replace d_open=d_stgp if d_open==. (654 real changes made) . replace d_open=99 if real(substr(stgp,1,1))>2 & real(substr(stgp,1,1))<6 (180 real changes made) . . gen d_first=min(d_4to3,d_4,d_5to43,d_5to41) (31096 missing values generated) . replace d_first=min(d_3to1,d_2to1,d_3to2) if d_first==. (26213 real changes made) . replace d_first=d_6 if d_first==. & real(substr(stgp,1,1))>1 & real(substr(st > gp,1,1))~=. (2816 real changes made) . replace d_first=d_stgp if d_first==. & real(substr(stgp,1,1))>1 & real(substr > (stgp,1,1))~=. (261 real changes made) . . *drop repeated observations . sort statefips intst mpost . drop if intst==intst[_n+1] & mpost==mpost[_n+1] (674 observations deleted) . . *drop all observations not on integer miles . drop if int(mpost)~=mpost (15507 observations deleted) . . *This line is needed to get us back to a reasonable length . *of the 1948 highway system . replace newrd=1 if real(intst)>100 & real(intst)~=. (3566 real changes made) . . save ../data/form500.dta,replace file ../data/form500.dta saved . . * Erase temporary and unneeded datasets . erase temp.dta . erase tempx.dta . erase ../data/roads_final.dta . erase ../data/routelog.dta . . log close log: /home/nate/roads/CD-ROM/programs/form500-cons.log log type: text closed on: 14 Aug 2005, 09:38:06 -------------------------------------------------------------------------------