clear
set mem 2000m
set matsize 800
set more off
capture log close
set logtype text
set trace off

#delimit;

global path ~;

log using $path/ui/logs/bn_comparison.log, replace;

/*=======================================
 Program: bn_master.do
 Author:  Avi Ebenstein
 Created: May 2005
 Purpose: Create Stata files from
	    data provided by Andy Spizak.
========================================*/

********************;
* Bring in BAM data ;
********************;

insheet 
cstate
cbatch
cidx
cm1	
cm2	
cm3	
cm4	
cm5	
cm6	
cm7	
cm8	
cm9	
cm10	
cm11	
cm12	
cm13	
cm14	
cm15	
cm16	
cm17	
cm18	
cm19	
cm20	
cm21	
cm22	
cm23	
cm24	
cm25	
cm26	
cm27	
cm28	
cm29	
cm30	
cm31	
cm32	
cm33	
cm34	
cm35	
cm36	
cm37	
cm38	
cm39	
cm40	
cm41	
cm42	
cdp
v47
using $path/ui/BN_DATA/rawfiles/bn_comparison_pool.tbl, delimiter("|");
compress;
save $path/ui/BN_DATA/datafiles/bn_comparison.dta, replace;

use  $path/ui/BN_DATA/datafiles/bn_comparison.dta, replace;
do   $path/ui/BN_DATA/dofiles/bn_comparison_labels.do;
rename cstate state;
rename cbatch batch;
sort state batch;
save $path/ui/BN_DATA/datafiles/bn_comparison_temp.dta, replace;

**************************************;
* Make a dataset of state X year      ;
**************************************;

use $path/ui/BN_DATA/datafiles/bn_comparison_temp.dta;
tostring batch, replace;
gen batchyear=substr(batch,1,4);
gen batchweek=substr(batch,5,2);

destring batchyear, gen(year);
destring batchweek, gen(week);

gen month=0;
replace month=1 if week>=1 & week<=4;
replace month=2 if week>=5 & week<=8;
replace month=3 if week>=9 & week<=12;
replace month=4 if week>=13 & week<=15;
replace month=5 if week>=16 & week<=19;
replace month=6 if week>=20 & week<=23;
replace month=7 if week>=24 & week<=27;
replace month=8 if week>=28 & week<=31;
replace month=9 if week>=32 & week<=35;
replace month=10 if week>=36 & week<=39;
replace month=11 if week>=40 & week<=43;
replace month=12 if week>=44 & week<=53;

egen qtr=cut(week), at(1,14,27,40,54);
egen quarter=group(qtr);

**************************************;
* Rename variables that are important ; 
**************************************;

rename cm2 uiweeks;
rename cm4 uidollars;

rename cm20 age0_24;
rename cm22 age25_34;
rename cm24 age35_44;
rename cm26 age45_64;
rename cm28 age65;

rename cm32 dol0_50;
rename cm34 dol51_100;
rename cm36 dol101_150;
rename cm38 dol151_200;
rename cm40 dol201;

rename cm8  males;
rename cm10 females;
rename cm14 whites;
rename cm16 blacks;

gen recipients=males+females;
save ~/ui/BN_DATA/datafiles/bn_comparison_clean, replace;

use ~/ui/BN_DATA/datafiles/bn_comparison_clean, replace;

/* March */
keep if week>=12 & week<=16;
collapse (mean) uiweeks (mean) uidollars (mean) dol* (mean) age*
(mean) males (mean) females (mean) whites (mean) blacks
, by(state year);

*****************************************;
* Some states/years are missing age info ;
* (AL, GA, LA, NM)                       ;
*****************************************;

egen agetot=rsum(age0_24 age25_34 age35_44 age45_64 age65);
gen agecat1=age0_24/agetot;
gen agecat2=age25_34/agetot;
gen agecat3=age35_44/agetot;
gen agecat4=age45_64/agetot;
gen agecat5=age65/agetot;

for num 1/5: replace agecatX=0 if agecatX==. & agetot==0;

egen doltot=rsum(dol0_50 dol51_100 dol101_150 dol151_200 dol201);
gen dolcat1=dol0_50/doltot;
gen dolcat2=dol51_100/doltot;
gen dolcat3=dol101_150/doltot;
gen dolcat4=dol151_200/doltot;
gen dolcat5=dol201/doltot;

gen nonwhite=blacks/(whites+blacks);
gen female=females/(males+females);
gen recipients=males+females;

label var uiweeks     "Number of weeks of unemployed recipiency, BN";
label var uidollars   "Amount of UI benefits, BN";

label var dolcat1 "Fraction of recipients <$50, BN";
label var dolcat2 "Fraction of recipients $51<X<$100, BN";
label var dolcat3 "Fraction of recipients $101<X<$150, BN";
label var dolcat4 "Fraction of recipients $151<X<$200, BN";
label var dolcat5 "Fraction of recipients $201+, BN";

label var agecat1 "Fraction of recipients age <25, BN";
label var agecat2 "Fraction of recipients age 25<X<34, BN";
label var agecat3 "Fraction of recipients age 35<X<44, BN";
label var agecat4 "Fraction of recipients age 45<X<64, BN";
label var agecat5 "Fraction of recipients age 65+, BN";
 
label var dol0_50     "Number of recipients <$50, BN";
label var dol51_100  "Number of recipients $51<X<$100, BN";
label var dol101_150 "Number of recipients $101<X<$150, BN";
label var dol151_200 "Number of recipients $151<X<$200, BN";
label var dol201     "Number of recipients $201+, BN";

label var age0_24 "Number of recipients age <25, BN";
label var age25_34 "Number of recipients age 25<X<34, BN";
label var age35_44 "Number of recipients age 35<X<44, BN";
label var age45_64 "Number of recipients age 45<X<64, BN";
label var age65 "Number of recipients age 65+, BN";

label var whites "Number of white recipients, BN";
label var blacks "Number of black recipients, BN";
label var males "Number of male recipients, BN";
label var females "Number of female recipients, BN";

label var nonwhite "Fraction of recipients nonwhite, BN";
label var female "Fraction of recipients female, BN";

drop doltot agetot;

label var recipients "Number of total recipients, BN";

format age* dol* whites blacks males females nonwhite female %9.3f;
format recipients %9.0fc;
format year %9.0f;

save $path/ui/BN_DATA/datafiles/bn_comp_state_year, replace;

***************;
* Quarterly!!  ;
***************;

use ~/ui/BN_DATA/datafiles/bn_comparison_clean, replace;

collapse (mean) uiweeks (mean) uidollars (mean) dol* (mean) age*
(mean) males (mean) females (mean) whites (mean) blacks
, by(state year quarter);

*****************************************;
* Some states/years are missing age info ;
* (AL, GA, LA, NM)                       ;
*****************************************;

egen agetot=rsum(age0_24 age25_34 age35_44 age45_64 age65);
gen agecat1=age0_24/agetot;
gen agecat2=age25_34/agetot;
gen agecat3=age35_44/agetot;
gen agecat4=age45_64/agetot;
gen agecat5=age65/agetot;

for num 1/5: replace agecatX=0 if agecatX==. & agetot==0;

egen doltot=rsum(dol0_50 dol51_100 dol101_150 dol151_200 dol201);
gen dolcat1=dol0_50/doltot;
gen dolcat2=dol51_100/doltot;
gen dolcat3=dol101_150/doltot;
gen dolcat4=dol151_200/doltot;
gen dolcat5=dol201/doltot;

gen nonwhite=blacks/(whites+blacks);
gen female=females/(males+females);
gen recipients=males+females;

label var uiweeks     "Number of weeks of unemployed recipiency, BN";
label var uidollars   "Amount of UI benefits, BN";

label var dolcat1 "Fraction of recipients <$50, BN";
label var dolcat2 "Fraction of recipients $51<X<$100, BN";
label var dolcat3 "Fraction of recipients $101<X<$150, BN";
label var dolcat4 "Fraction of recipients $151<X<$200, BN";
label var dolcat5 "Fraction of recipients $201+, BN";

label var agecat1 "Fraction of recipients age <25, BN";
label var agecat2 "Fraction of recipients age 25<X<34, BN";
label var agecat3 "Fraction of recipients age 35<X<44, BN";
label var agecat4 "Fraction of recipients age 45<X<64, BN";
label var agecat5 "Fraction of recipients age 65+, BN";
 
label var dol0_50     "Number of recipients <$50, BN";
label var dol51_100  "Number of recipients $51<X<$100, BN";
label var dol101_150 "Number of recipients $101<X<$150, BN";
label var dol151_200 "Number of recipients $151<X<$200, BN";
label var dol201     "Number of recipients $201+, BN";

label var age0_24 "Number of recipients age <25, BN";
label var age25_34 "Number of recipients age 25<X<34, BN";
label var age35_44 "Number of recipients age 35<X<44, BN";
label var age45_64 "Number of recipients age 45<X<64, BN";
label var age65 "Number of recipients age 65+, BN";

label var whites "Number of white recipients, BN";
label var blacks "Number of black recipients, BN";
label var males "Number of male recipients, BN";
label var females "Number of female recipients, BN";

label var nonwhite "Fraction of recipients nonwhite, BN";
label var female "Fraction of recipients female, BN";

label var quarter "Quarter of the year";
drop doltot agetot;

label var recipients "Number of total recipients, BN";

format age* dol* quarter whites blacks males females nonwhite female %9.3f;
format recipients %9.0fc;
format year %9.0f;
format quarter %9.0f;
save $path/ui/BN_DATA/datafiles/bn_comp_quarter_year, replace;

/* I wonder if the following code is accurate */

use ~/ui/BN_DATA/datafiles/bn_comparison_clean, replace;
collapse (sum) recipients (mean) year (mean) month (mean) quarter, by(batch) fast;
collapse (mean) recipients,by(year quarter) fast;
save ~/ui/BN_DATA/datafiles/recipients_year_quarter, replace;

use ~/ui/BN_DATA/datafiles/bn_comparison_clean, replace;
collapse (sum) recipients (mean) year (mean) month (mean) quarter, by(batch) fast;
collapse (mean) recipients,by(year) fast;
save ~/ui/BN_DATA/datafiles/recipients_year, replace;