insheet using BEC_PHOTO1.txt, tab gen BEC = 1 gen STR = 0 gen RUF = 0 gen GUR = 0 gen PHOTO = 1 gen id = _n gen date = v1 if mod(id,4)==3 gen price_dollar = v1 if mod(id,4)==2 gen price_pound = v1 if mod(id,4)==1 gen lot_number = v1 if mod(id,4)==0 gen size = v2 if mod(id,4)==2 gen price_est = v2 if mod(id,4)==0 gen auction_house = v2 if mod(id,4)==3 gen rec_id = int((id-1)/4)+1 replace date = date[(rec_id[_n]-1)*4+3] replace price_dollar = price_dollar[(rec_id[_n]-1)*4+2] replace price_pound = price_pound[(rec_id[_n]-1)*4+1] replace lot_number = lot_number[(rec_id[_n]-1)*4+4] replace size = size[(rec_id[_n]-1)*4+2] replace auction_house = auction_house[(rec_id[_n]-1)*4+3] replace price_est = price_est[(rec_id[_n]-1)*4+4] drop if rec_id == rec_id[_n+1] replace price_dollar = substr(price_dollar, strpos(price_dollar, " ")+1,.) replace price_dollar = substr(price_dollar, 1, strpos(price_dollar, ",")-1)+substr(price_dollar, strpos(price_dollar, ",")+1,.) replace price_dollar = substr(price_dollar, 1, strpos(price_dollar, ",")-1)+substr(price_dollar, strpos(price_dollar, ",")+1,.) replace price_pound = substr(price_pound, strpos(price_pound, " ")+1,.) replace price_pound = substr(price_pound, 1, strpos(price_pound, ",")-1)+substr(price_pound, strpos(price_pound, ",")+1,.) replace price_pound = substr(price_pound, 1, strpos(price_pound, ",")-1)+substr(price_pound, strpos(price_pound, ",")+1,.) destring price_dollar, gen(p_dollar) destring price_pound, gen(p_pound) * Get price in other currency gen price_other = substr(date, 1, strpos(date, " ")-1) replace price_other = substr(price_other, 1, strpos(price_other, ",")-1)+substr(price_other, strpos(price_other, ",")+1,.) replace price_other = substr(price_other, 1, strpos(price_other, ",")-1)+substr(price_other, strpos(price_other, ",")+1,.) destring price_other, gen(p_other) gen currency = substr( price_est, length( price_est)-3,4) replace p_other = p_pound if currency=="UK.P" gen xrate = p_dollar/p_other replace xrate = 1 if currency == "US.D" * Get price estimates gen dash_ind = strpos(price_est, "-") gen est_min = word(price_est, 2) if dash_ind == 0 gen est_max = word(price_est, 2) if dash_ind == 0 replace est_min = word(price_est,2) if dash_ind != 0 replace est_max = word(price_est,4) if dash_ind != 0 replace est_max = substr(est_max, 1, strpos(est_max, ",")-1)+substr(est_max, strpos(est_max, ",")+1,.) replace est_max = substr(est_max, 1, strpos(est_max, ",")-1)+substr(est_max, strpos(est_max, ",")+1,.) replace est_min = substr(est_min, 1, strpos(est_min, ",")-1)+substr(est_min, strpos(est_min, ",")+1,.) replace est_min = substr(est_min, 1, strpos(est_min, ",")-1)+substr(est_min, strpos(est_min, ",")+1,.) destring est_max, gen(estimate_max) destring est_min, gen(estimate_min) gen p_est = ((estimate_min+estimate_max)/2)*xrate * Get Lot number gen lot = word(lot_number,2) destring lot, replace * Get dimensions gen d1 = substr( size, strpos( size, "(")+1, strpos( size, "x")-strpos( size, "(")-1) gen d2 = substr( size, strpos( size, "x")+1, strpos( size, "i")-strpos( size, "x")-1) gen erase = 0 replace erase = 1 if d1 == "?" | d2 == "?" replace d1 = "" if erase == 1 replace d2 = "" if erase == 1 destring d1 d2, gen (dim1 dim2) gen Size = dim1*dim2 gen Sizesqr = Size^2 * Dummies for Auction house gen CHR = strpos( auction_house, "Christie's") gen SOT = strpos( auction_house, "Sotheby's") gen OTH = 1 - (CHR + SOT) * Breaking date gen date2 = substr( date, strpos( date,"(")+1, strpos( date,")")-strpos( date,"(")-1) gen day = substr( date2, 1, strpos( date2,"-")-1) gen rest_date = substr( date2, strpos( date2,"-")+1,.) gen month = substr( rest_date, 1, strpos( rest_date,"-")-1) gen year = substr( rest_date, strpos( rest_date,"-")+1,.) drop rest_date quietly tab month, gen(dm_month_) quietly tab year, gen(dm_year_) drop v1 v2 v3 id date price_dollar price_pound lot_number size price_est auction_house rec_id price_other currency xrate dash_ind est_min estimate_min est_max estimate_max d1 d2 date2 save file8