clear
cd "/afs/econ.duke.edu/data/bayerp/Cal_Dataset_Append/"

set mem 1800m

set more off

*amended 3/25/08 to include lat/lon


use "/afs/econ.duke.edu/data/bayerp/California/match90_full.dta"
keep sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type


append using "/afs/econ.duke.edu/data/bayerp/California/match91_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match92_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match93_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match94_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match95_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match96_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match97_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match98_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match99_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match00_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match01_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match02_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match03_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace coapplicantrace applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

append using "/afs/econ.duke.edu/data/bayerp/California/match04_full.dta", keep (sa_x_coord sa_y_coord lendercode lname tract county loanamount tract1 loanamount1 loanamount1a id asofdate agencycode respid rname loantype loanamount0 msa applicantrace  applicantsex coapplicantsex applicantincome purchasertype seqnumber tract0 id3 tractdif loandif match flagcode flagcode2 sa_yr_blt sa_lotsize sa_sqft sa_nbr_bath sa_nbr_bedrms sa_nbr_rms sa_nbr_stories sa_nbr_units sa_date_transfer sa_val_transfer sr_unique_id max_sr_date_transfer sr_buyer sr_seller sr_loan_val_1 sr_loan_val_2 sr_loan_val_3 lendername sr_title_co_name sr_title_co_code sa_census_block_group mm_muni_code _merge property_id sa_yr_blt_effect sa_census_block_group sa_parcel_nbr_primary sa_site_street_name sr_tran_type)

* coapplicantrace missing from 2004

compress

gen parcelst = sa_parcel_nbr_primary
replace parcelst=subinstr(parcelst, "-", "", .)
replace parcelst=trim(parcelst)
replace parcelst=subinstr(parcelst, " ", "", .)

destring parcelst, generate(parcelnum) ignore("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z") 

replace parcelst = "9999999999999" if parcelst==""
replace parcelnum = 9999999999999 if parcelnum==.

sort parcelst


save fullmatch_90_04, replace