/* NEED TO INPUT LINE TO CONNECT TO DATABASE */

/* set directories */
libname home '/user/patents/build/sas';
%let csvdir= %str(NEED TO INPUT YOUR VALUE HERE);
options obs=max;

/* compute all required variables */
%macro quartiles;
	data quarts;
		set home.quartile_variables_sorted;

		/* first compute the quartiles */
		quartile = .;
		wages=.;
		%do year=1996 %to 2014;
			if tax_yr = &year. then
			do;
				wages = wages&year.;
				if (p25_v ne .) and (wages ne 0 and wages ne .) then
				do;
					if (wages <= p25_v) then quartile=1;
					else if (wages <= p50_v) then quartile=2;
					else if (wages <= p75_v) then quartile=3;
					else quartile=4;
				end;
			end;
		%end;
		drop p25_v p50_v p75_v;

		/* cohort wages for groups */
		%do year=1996 %to 2014;
			chtwages_&year.=wages&year.;
			if chtwages_&year.=. then chtwages_&year.=0;
		%end;

		/* entrants and wages 3 years out */
		ent_plus3=0;
		ent_plus3_wagegrowth=.;
		ent_plus3_wagegrowth_dhs=.;
		%do year=2004 %to 2014;

			%let prehireyear=%eval(&year.-4);
			%let hireyear=%eval(&year.-3);

			if tax_yr=&year. then
			do;
				if payer_tin&prehireyear. ne payer_tin&hireyear. and payer_tin_w2_max=payer_tin&hireyear. then
				do;
					ent_plus3=1;
					ent_plus3_wagegrowth = wages-wages&hireyear.;
					ent_plus3_wagegrowth_dhs = 2*(wages-wages&hireyear.)/(wages+wages&hireyear.);
				end;
			end;
		%end;

		/* to make data set smaller drop unnecessary variables */
		drop wages1996 wages1997 wages1998 wages1999 wages2000 wages2001 wages2002 wages2003 wages2004 wages2005 wages2006 wages2007 wages2008 wages2009 wages2010 wages2011 wages2012 wages2013 wages2014 payer_tin1996 payer_tin1997 payer_tin1998 payer_tin1999 payer_tin2000 payer_tin2001 payer_tin2002 payer_tin2003 payer_tin2004 payer_tin2005 payer_tin2006 payer_tin2007 payer_tin2008 payer_tin2009 payer_tin2010 payer_tin2011 payer_tin2012 payer_tin2013 payer_tin2014;

	run;
%mend;
%quartiles;

/* application year quartiles for cohort wages: input variables */
%let inp_qvars=
payer_tin_w2_max,
tax_yr,
quartile,
chtwages_1996,
chtwages_1997,
chtwages_1998,
chtwages_1999,
chtwages_2000,
chtwages_2001,
chtwages_2002,
chtwages_2003,
chtwages_2004,
chtwages_2005,
chtwages_2006,
chtwages_2007,
chtwages_2008,
chtwages_2009,
chtwages_2010,
chtwages_2011,
chtwages_2012,
chtwages_2013,
chtwages_2014;

/* application year quartiles for cohort wages: output variables */
%let outp_qvars=
payer_tin_w2_max,
tax_yr,
quartile,
mean(chtwages_1996) as cht_q_wages_1996,
mean(chtwages_1997) as cht_q_wages_1997,
mean(chtwages_1998) as cht_q_wages_1998,
mean(chtwages_1999) as cht_q_wages_1999,
mean(chtwages_2000) as cht_q_wages_2000,
mean(chtwages_2001) as cht_q_wages_2001,
mean(chtwages_2002) as cht_q_wages_2002,
mean(chtwages_2003) as cht_q_wages_2003,
mean(chtwages_2004) as cht_q_wages_2004,
mean(chtwages_2005) as cht_q_wages_2005,
mean(chtwages_2006) as cht_q_wages_2006,
mean(chtwages_2007) as cht_q_wages_2007,
mean(chtwages_2008) as cht_q_wages_2008,
mean(chtwages_2009) as cht_q_wages_2009,
mean(chtwages_2010) as cht_q_wages_2010,
mean(chtwages_2011) as cht_q_wages_2011,
mean(chtwages_2012) as cht_q_wages_2012,
mean(chtwages_2013) as cht_q_wages_2013,
mean(chtwages_2014) as cht_q_wages_2014,
count(*) as cht_quartile_size;

/* create the app year cht wages table by quartile */
proc sql;
	create table chtqappyr as
	select &outp_qvars.
	from
	(select &inp_qvars. from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and quartile<>.)
	as b
	group by b.payer_tin_w2_max, b.tax_yr, b.quartile
	order by payer_tin_w2_max, tax_yr, quartile;
quit;

proc export data=chtqappyr outfile="&csvdir./cohort_appyr_q_wages.csv" dbms=csv replace;
run;

/* drop the table */
proc sql;
	drop table chtqappyr;
quit;

/* entrant wage growth three years out: input variables*/
%let inp_entplus3_vars=
payer_tin_w2_max,
tax_yr,
ent_plus3_wagegrowth,
ent_plus3_wagegrowth_dhs,
ent_plus3;

/* entrant wage growth three years out: output variables */
%let outp_entplus3_vars=
payer_tin_w2_max,
tax_yr,
mean(ent_plus3_wagegrowth) as ent_wage_growth_in3,
mean(ent_plus3_wagegrowth_dhs) as ent_wage_growth_in3_dhs,
sum(ent_plus3) as ents_who_stay_3yrs;

/* create the app year cht wages table by quartile */
proc sql;
	create table entplus3 as
	select &outp_entplus3_vars.
	from
	(select &inp_entplus3_vars.
		from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>.)
	as b
	group by b.payer_tin_w2_max, b.tax_yr
	order by payer_tin_w2_max, tax_yr;
quit;

proc export data=entplus3 outfile="&csvdir./entrant_wage_growth.csv" dbms=csv replace;
run;

/* drop the table */
proc sql;
	drop table entplus3;
quit;
