data one; *written 3/23/04 after monumental effort starting July, 2002; * rewritten 12/8/04 after correcting hlat, hlong & adding varbs; * rewritten again 1/30/06 after adding private school varbs; * rewritten again 3/13/06 after adding newcomer varbs; infile 'c:\oldc\2000data\2000data1.csv' dlm=',' lrecl=3000; input j irn pticode loccode msa houselat houselong Oricode countyfips cosub place tract blkgrp block cd106 Cbglat Cbglong trandate tranamt onestory brick finbase dgarage air fire outbuildings rooms bedrooms fullbath partbath agehouse buildingsqft lotsize porches patioo deck poole mortamt prevsaledatec Zip Mortterm Mortrate Mortvarfreq morttype $ financing $ Prevsaleamt groupid fadm_sd spadm_sd pctmin_sd pctadc_sd avginc_sd valpup_sd totrevpup strevpup locrevpup xpup_sd effmills_sd tchpay_sd ptratio_sd psfratio_sd sfpctmin_sd pcttchnoba_sd pcttchba_sd pcttch150_sd pcttchmast_sd tchexp_sd attrate_sd sfattrate_sd droprte_sd cprate_sd pctmartwowage pctdepfilers pctsinsd pctsinparsd pctsinnonparsd pcttwowagesd pctmarsd pctbelow4cit00 pctprof4cit00 pctadv4cit00 pctbelow4cit99 pctprof4cit99 pctadv4cit99 pctbelow4cit98 pctprof4cit98 pctadv4cit98 pctbelow4math00 pctprof4math00 pctadv4math00 pctbelow4math99 pctprof4math99 pctadv4math99 pctbelow4math98 pctprof4math98 pctadv4math98 pctbelow4read00 pctprof4read00 pctadv4read00 pctbelow4read99 pctprof4read99 pctadv4read99 pctbelow4read98 pctprof4read98 pctadv4read98 pctbelow4write00 pctprof4write00 pctadv4write00 pctbelow4write99 pctprof4write99 pctadv4write99 pctbelow4write98 pctprof4write98 pctadv4write98 pctbelow4sci00 pctprof4sci00 pctadv4sci00 pctbelow4sci99 pctprof4sci99 pctadv4sci99 pctbelow4sci98 pctprof4sci98 pctadv4sci98 pctbelow6cit00 pctprof6cit00 pctadv6cit00 pctbelow6cit99 pctprof6cit99 pctadv6cit99 pctbelow6cit98 pctprof6cit98 pctadv6cit98 pctbelow6math00 pctprof6math00 pctadv6math00 pctbelow6math99 pctprof6math99 pctadv6math99 pctbelow6math98 pctprof6math98 pctadv6math98 pctbelow6read00 pctprof6read00 pctadv6read00 pctbelow6read99 pctprof6read99 pctadv6read99 pctbelow6read98 pctprof6read98 pctadv6read98 pctbelow6write00 pctprof6write00 pctadv6write00 pctbelow6write99 pctprof6write99 pctadv6write99 pctbelow6write98 pctprof6write98 pctadv6write98 pctbelow6sci00 pctprof6sci00 pctadv6sci00 pctbelow6sci99 pctprof6sci99 pctadv6sci99 pctbelow6sci98 pctprof6sci98 pctadv6sci98 pctbelow2cit00 pctprof2cit00 pctadv2cit00 pctbelow2cit99 pctprof2cit99 pctadv2cit99 pctbelow2cit98 pctprof2cit98 pctadv2cit98 pctbelow2math00 pctprof2math00 pctadv2math00 pctbelow2math99 pctprof2math99 pctadv2math99 pctbelow2math98 pctprof2math98 pctadv2math98 pctbelow2read00 pctprof2read00 pctadv2read00 pctbelow2read99 pctprof2read99 pctadv2read99 pctbelow2read98 pctprof2read98 pctadv2read98 pctbelow2write00 pctprof2write00 pctadv2write00 pctbelow2write99 pctprof2write99 pctadv2write99 pctbelow2write98 pctprof2write98 pctadv2write98 pctbelow2sci00 pctprof2sci00 pctadv2sci00 pctbelow2sci99 pctprof2sci99 pctadv2sci99 pctbelow2sci98 pctprof2sci98 pctadv2sci98 sdrating pct4all00 pct4all99 pct4all98 pct6all00 pct6all99 pct6all98 pct9all00 pct9all99 pct9all98 pct2all00 pct2all99 pct2all98 stattrte00 stattrte99 stattrte98 gradrte00 gradrte99 gradrte98 honorgrad_sd promote4_sd promote6_sd ptratio00_sd inareak8_sd inareahs_sd teachattrate00_sd tchpay00_sd locrevpup00_sd strevpup00_sd fedrevpup00_sd totrevpup00_sd instrucxp00_sd bldgxp00_sd adminxp00_sd pupsupportxp00_sd sfsupportxp00_sd totxp00_sd levies numlevies numleviespass alldisciplines expulsions withdrawals suspensions otherdiscipline pctagval00 pctresval00 pctcl1val00 pctcl2val00 totresval00 totcl1val00 totcl2val00 newconst1 newconst2 resvalpup00 cl1valpup00 cl2valpup00 newconstpup1 newconstpup2 pctagval99 pctresval99 pctcl1val99 pctcl2val99 totresval99 totcl1val99 totcl2val99 resvalpup99 cl1valpup99 cl2valpup99 grosstax00_sd class100_sd class200_sd yrssdytax sdytaxrate sdytax carbon_a_co nox_a_co pm10_a_co so2_a_co voc_a_co pm25_a_co ammonia_a_co carbon_ps_co nox_ps_co pm10_ps_co so2_ps_co voc_ps_co pm25_ps_co ammonia_ps_co mindist fugair_ct stackair_ct totair_ct waterpoll_ct inject_ct landrelease_ct onsite_ct offsite_ct stfa_tot_ct fugair_cbg stackair_cbg totair_cbg waterpoll_cbg inject_cbg landrelease_cbg onsite_cbg offsite_cbg stfa_tot_cbg Carbon_ct Nox_ct Pm10_ct So2_ct Voc_ct Pm25_ct Ammonia_ct Netrank_air_ct Carbon_cbg Nox_cbg Pm10_cbg So2_cbg Voc_cbg Pm25_cbg Ammonia_cbg Netrank_air_cbg pop_cbg pcturban_cbg density_cbg pctwhite_cbg pctblack_cbg pctapi_cbg pctother_cbg Pcthisp_cbg Leikrace_cbg Pctyoung_cbg Pct517_cbg Pct1864_cbg Pct65pls_cbg Agehet_cbg Pctkids_cbg Pctmar_cbg Pctsepdiv_cbg Pctsin_cbg Pctenglish_cbg Pctforeign_cbg Commute_cbg Privelem_cbg Privhs_cbg Priv_cbg pcths_cbg pctlesba_cbg pctba_cbg Pctgraddeg_cbg Pctphd_cbg Pctnohs_cbg Leikeduc_cbg Unemp_cbg Bluecoll_cbg Mediany_cbg Avginc_cbg Inchet_cbg Pctwelfare_cbg Familyinc_cbg Percapinc_cbg Poverty_cbg childpoverty_cbg Hu100_cbg Ruralhsg_cbg Pctvacant_cbg Ownerocc_cbg nocrowdhsg_cbg Rooms_cbg Pctdetached_cbg Agehsg_cbg Nocar_cbg Onecar_cbg Twocar_cbg Threecar_cbg Fourcar_cbg Fiveplscar_cbg Avgcar_cbg Hphet_cbg Hp_cbg Pctnomort_cbg Mohsgcost1_cbg Mohsgcost2_cbg clearratio clearratio2 clearratio3 offensecap offensecap2 offensecap3 policeratio policeratio2 policeratio3 policeemprat policeemprat2 policeemprat3 pcchange streetnum streetname $ city $ state $ mat_type hlat hlong catholic noncatholic priv_irn priv_lat priv_long tuition priv_math censusidpriv priv_dist priv_irn_wp tuition_wp priv_math_wp censusidpriv_wp priv_dist_wp pctlt1_ct pctlt6_ct; proc means; var j irn pticode loccode msa houselat houselong Oricode countyfips cosub place tract blkgrp block cd106 Cbglat Cbglong trandate tranamt onestory brick finbase dgarage air fire outbuildings rooms bedrooms fullbath partbath agehouse buildingsqft lotsize porches patioo deck poole mortamt prevsaledatec Zip Mortterm Mortrate Mortvarfreq Prevsaleamt groupid fadm_sd spadm_sd pctmin_sd pctadc_sd avginc_sd valpup_sd totrevpup strevpup locrevpup xpup_sd effmills_sd tchpay_sd ptratio_sd psfratio_sd sfpctmin_sd pcttchnoba_sd pcttchba_sd pcttch150_sd pcttchmast_sd tchexp_sd attrate_sd sfattrate_sd droprte_sd cprate_sd pctmartwowage pctdepfilers pctsinsd pctsinparsd pctsinnonparsd pcttwowagesd pctmarsd pctbelow4cit00 pctprof4cit00 pctadv4cit00 pctbelow4cit99 pctprof4cit99 pctadv4cit99 pctbelow4cit98 pctprof4cit98 pctadv4cit98 pctbelow4math00 pctprof4math00 pctadv4math00 pctbelow4math99 pctprof4math99 pctadv4math99 pctbelow4math98 pctprof4math98 pctadv4math98 pctbelow4read00 pctprof4read00 pctadv4read00 pctbelow4read99 pctprof4read99 pctadv4read99 pctbelow4read98 pctprof4read98 pctadv4read98 pctbelow4write00 pctprof4write00 pctadv4write00 pctbelow4write99 pctprof4write99 pctadv4write99 pctbelow4write98 pctprof4write98 pctadv4write98 pctbelow4sci00 pctprof4sci00 pctadv4sci00 pctbelow4sci99 pctprof4sci99 pctadv4sci99 pctbelow4sci98 pctprof4sci98 pctadv4sci98 pctbelow6cit00 pctprof6cit00 pctadv6cit00 pctbelow6cit99 pctprof6cit99 pctadv6cit99 pctbelow6cit98 pctprof6cit98 pctadv6cit98 pctbelow6math00 pctprof6math00 pctadv6math00 pctbelow6math99 pctprof6math99 pctadv6math99 pctbelow6math98 pctprof6math98 pctadv6math98 pctbelow6read00 pctprof6read00 pctadv6read00 pctbelow6read99 pctprof6read99 pctadv6read99 pctbelow6read98 pctprof6read98 pctadv6read98 pctbelow6write00 pctprof6write00 pctadv6write00 pctbelow6write99 pctprof6write99 pctadv6write99 pctbelow6write98 pctprof6write98 pctadv6write98 pctbelow6sci00 pctprof6sci00 pctadv6sci00 pctbelow6sci99 pctprof6sci99 pctadv6sci99 pctbelow6sci98 pctprof6sci98 pctadv6sci98 pctbelow2cit00 pctprof2cit00 pctadv2cit00 pctbelow2cit99 pctprof2cit99 pctadv2cit99 pctbelow2cit98 pctprof2cit98 pctadv2cit98 pctbelow2math00 pctprof2math00 pctadv2math00 pctbelow2math99 pctprof2math99 pctadv2math99 pctbelow2math98 pctprof2math98 pctadv2math98 pctbelow2read00 pctprof2read00 pctadv2read00 pctbelow2read99 pctprof2read99 pctadv2read99 pctbelow2read98 pctprof2read98 pctadv2read98 pctbelow2write00 pctprof2write00 pctadv2write00 pctbelow2write99 pctprof2write99 pctadv2write99 pctbelow2write98 pctprof2write98 pctadv2write98 pctbelow2sci00 pctprof2sci00 pctadv2sci00 pctbelow2sci99 pctprof2sci99 pctadv2sci99 pctbelow2sci98 pctprof2sci98 pctadv2sci98 sdrating pct4all00 pct4all99 pct4all98 pct6all00 pct6all99 pct6all98 pct9all00 pct9all99 pct9all98 pct2all00 pct2all99 pct2all98 stattrte00 stattrte99 stattrte98 gradrte00 gradrte99 gradrte98 honorgrad_sd promote4_sd promote6_sd ptratio00_sd inareak8_sd inareahs_sd teachattrate00_sd tchpay00_sd locrevpup00_sd strevpup00_sd fedrevpup00_sd totrevpup00_sd instrucxp00_sd bldgxp00_sd adminxp00_sd pupsupportxp00_sd sfsupportxp00_sd totxp00_sd levies numlevies numleviespass alldisciplines expulsions withdrawals suspensions otherdiscipline pctagval00 pctresval00 pctcl1val00 pctcl2val00 totresval00 totcl1val00 totcl2val00 newconst1 newconst2 resvalpup00 cl1valpup00 cl2valpup00 newconstpup1 newconstpup2 pctagval99 pctresval99 pctcl1val99 pctcl2val99 totresval99 totcl1val99 totcl2val99 resvalpup99 cl1valpup99 cl2valpup99 grosstax00_sd class100_sd class200_sd yrssdytax sdytaxrate sdytax carbon_a_co nox_a_co pm10_a_co so2_a_co voc_a_co pm25_a_co ammonia_a_co carbon_ps_co nox_ps_co pm10_ps_co so2_ps_co voc_ps_co pm25_ps_co ammonia_ps_co mindist fugair_ct stackair_ct totair_ct waterpoll_ct inject_ct landrelease_ct onsite_ct offsite_ct stfa_tot_ct fugair_cbg stackair_cbg totair_cbg waterpoll_cbg inject_cbg landrelease_cbg onsite_cbg offsite_cbg stfa_tot_cbg Carbon_ct Nox_ct Pm10_ct So2_ct Voc_ct Pm25_ct Ammonia_ct Netrank_air_ct Carbon_cbg Nox_cbg Pm10_cbg So2_cbg Voc_cbg Pm25_cbg Ammonia_cbg Netrank_air_cbg pop_cbg pcturban_cbg density_cbg pctwhite_cbg pctblack_cbg pctapi_cbg pctother_cbg Pcthisp_cbg Leikrace_cbg Pctyoung_cbg Pct517_cbg Pct1864_cbg Pct65pls_cbg Agehet_cbg Pctkids_cbg Pctmar_cbg Pctsepdiv_cbg Pctsin_cbg Pctenglish_cbg Pctforeign_cbg Commute_cbg Privelem_cbg Privhs_cbg Priv_cbg pcths_cbg pctlesba_cbg pctba_cbg Pctgraddeg_cbg Pctphd_cbg Pctnohs_cbg Leikeduc_cbg Unemp_cbg Bluecoll_cbg Mediany_cbg Avginc_cbg Inchet_cbg Pctwelfare_cbg Familyinc_cbg Percapinc_cbg Poverty_cbg childpoverty_cbg Hu100_cbg Ruralhsg_cbg Pctvacant_cbg Ownerocc_cbg nocrowdhsg_cbg Rooms_cbg Pctdetached_cbg Agehsg_cbg Nocar_cbg Onecar_cbg Twocar_cbg Threecar_cbg Fourcar_cbg Fiveplscar_cbg Avgcar_cbg Hphet_cbg Hp_cbg Pctnomort_cbg Mohsgcost1_cbg Mohsgcost2_cbg clearratio clearratio2 clearratio3 offensecap offensecap2 offensecap3 policeratio policeratio2 policeratio3 policeemprat policeemprat2 policeemprat3 pcchange streetnum mat_type hlat hlong catholic noncatholic priv_irn priv_lat priv_long tuition priv_math censusidpriv priv_dist priv_irn_wp tuition_wp priv_math_wp censusidpriv_wp priv_dist_wp pctlt1_ct pctlt6_ct; *1/30/06 the remaining stuff just checks to make sure everything looks right; *proc freq; *tables morttype financing streetnum; data two; set one; *see if non-numeric varbs streetname city state work fine (for valid j's) 12/5/04; if j=. then delete; proc print data=two (obs=100); var j streetnum streetname city state mat_type; data two; set one; *also see if EZLocate geocoded varbs have valid census data 12/6/04; if j=. then delete; if not (mat_type =1 or mat_type=2) then delete; proc print data=two (obs=101); var j countyfips tract blkgrp hlat hlong; proc means; var j countyfips tract blkgrp hlat hlong; data two; set one; *now see if non-EZLocate geocoded varbs have valid census data 12/6/04; if j=. then delete; if blkgrp=. then delete; if (mat_type =1 or mat_type=2) then delete; proc print data=two (obs=102); var j countyfips tract blkgrp hlat hlong; proc means; var j countyfips tract blkgrp hlat hlong; data two; set one; *see sample of houses with valid j's that didn't get geocoded 12/8/04; if not (j= 3712 or j=3713 or j=3715 or j=3728 or j=3738 or j=3743 or j=3744 or j=3747 or j=3749 or j=4113 or j=4116 or j=4117 or j=4118 or j=4121 or j=4122 or j=4123) then delete; proc print; var air streetnum streetname city state lotsize zip houselat agehouse; data two; set one; *finally verify that houselat and houselong are very similar to hlat hlong; * for duplicate houselats 12/8/04; proc sort; by houselat; data three; set two; by houselat; retain ct 0; if first.houselat then ct=1; else ct=ct+1; if not (ct=>2) then delete; if j=. then delete; if houselat=. then delete; proc print data=three (obs=103); var j houselat hlat houselong hlong; proc means; var j houselat hlat houselong hlong; data four; set one; *extra check of houselat v hlat 12/8/04; *all these j's but 59757 showed up as having duplicate houselats; if not (j=9310 or j=9311 or j=116941 or j=116951 or j=116910 or j=116933 or j=59757) then delete; proc print; var j houselat hlat; proc freq; tables hlat houselat; *1/30/06 see if private school variables got added okay; data private; set one; proc means; var j priv_irn priv_lat priv_long tuition priv_math censusidpriv priv_dist priv_irn_wp tuition_wp priv_math_wp censusidpriv_wp priv_dist_wp countyfips catholic noncatholic fullbath j hlat hlong; *3/13/06 see if newcomer variables got added okay; data newcomer; set one; proc univariate; var pctlt1_ct pctlt6_ct; data newcomer2; set one; if pctlt1_ct = . then delete; proc print data=newcomer2 (obs=150); var j pctlt1_ct pctlt6_ct priv_dist priv_math_wp noncatholic fullbath j hlat hlong; run; quit;