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

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

/*load the data file*/
data quarts;
	set home.quartile_variables_sorted;
run;

/* add the wages into the full data set */
%macro getwages;
	data quarts;
		set quarts;
		wages = .;

		%do year=1996 %to 2014;

			/* replace with missing if wages == 0 */
			if wages&year. = 0 then wages&year. = .;

			/* fill in wages based on year */
			if tax_yr = &year. then wages = wages&year.;

		%end;
	run;
%mend;
%getwages;

/* import the basedata into the working directory */
%macro cleanfulldata;
	data forquartloop;
		set quarts;
		if tax_yr ne 2000 then delete;
	run;
%mend;
%cleanfulldata;

/* start with the 1996 data */
proc means data=forquartloop noprint nway;
	class payer_tin1996;
	var wages1996;
	output out=basetins(keep=payer_tin1996 _FREQ_ p25_v p50_v p75_v rename=(payer_tin1996=payer_tin_w2_max)) p25=p25_v p50=p50_v p75=p75_v;
run;

data basetins;
	set basetins;
	tax_yr = 1996;
	if _FREQ_ < 4 then
		do; 
			p25_v = .;
			p50_v = .;
			p75_v = .;
		end;
run;

%macro quartiles;
	%do year=1997 %to 2014;

		/* find the within group means by payer_tin_w2_max in the given year */
		proc means data=forquartloop noprint nway;
			class payer_tin&year.;
			var wages&year.;
			output out=temp(keep=payer_tin&year. _FREQ_ p25_v p50_v p75_v rename=(payer_tin&year.=payer_tin_w2_max)) p25=p25_v p50=p50_v p75=p75_v;
		run;

		data temp;
			set temp;
			tax_yr = &year.;
			if _FREQ_ < 4 then
				do; 
					p25_v = .;
					p50_v = .;
					p75_v = .;
				end;
		run;

		/* merge to the list of payer tins */
		proc append base=basetins data=temp;
		run;

	%end;
%mend;
%quartiles;

/* sort basetins */ 
proc sort data=basetins out=basetins(drop=_freq_);
	by payer_tin_w2_max tax_yr;
run;

/* can delete temp and forquartloop now */
proc sql;
	drop table temp;
quit;

proc sql;
	drop table forquartloop;
quit;

/* merge the computed p25, p50, and p75 to the quarts data set using hash tables for performance */
data quarts;
	merge quarts basetins;
	by payer_tin_w2_max tax_yr;
run;

/* can delete basetins now */
proc sql;
	drop table basetins;
quit;

%macro computequarts;
	data quarts;
		set quarts;
		quartile = .;
		%do year=1996 %to 2014;
			if (p25_v ne .) and (wages&year. ne 0 and wages&year. ne .) and (tax_yr = &year.) then
				do;
					if (wages&year. <= p25_v) then quartile=1;
					else if (wages&year. <= p50_v) then quartile=2;
					else if (wages&year. <= p75_v) then quartile=3;
					else quartile=4;
				end;
		%end;
		drop p25_v p50_v p75_v;
	run;
%mend;
%computequarts;

/* get the means after computing the quartiles */
proc sql;
	create table quartmeans as
	select payer_tin_w2_max, tax_yr, quartile, mean(wages) as mean_quartile_wages, count(*) as emp_quartile 
	from (select payer_tin_w2_max, tax_yr, quartile, wages 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;
quit;

data home.mean_quartile_wages;
	set quartmeans;
run;

proc export data=quartmeans outfile="&csvdir./mean_quartile_wages.csv" dbms=csv replace;
run;

/* now find the stayer quartile means */
%macro stayers;
	data stayertemp;
		set quarts;
		%do k=1996 %TO 2014;
			stayer&k.=0;
			if payer_tin_w2_max=payer_tin&k. then stayer&k.=1;
			stayerwages&k.=.;
			if stayer&k.=1 then stayerwages&k.=wages&k.;
			drop payer_tin&k. wages&k.;
		%end;
	run;
%mend;
%stayers;

/* generate the base for the merging */
proc sql;
	create table stayermeans as
	select payer_tin_w2_max, tax_yr, quartile, emp_quartile as cht_quartile_size
	from quartmeans
	order by payer_tin_w2_max, tax_yr, quartile;
quit;

/* can delete quartmeans now */
proc sql;
	drop table quartmeans;
quit;

/* set base values for the hash vars and the keep vars */
%let hashvars="payer_tin_w2_max", "tax_yr", "quartile", "cht_quartile_size";
%let keepvars= payer_tin_w2_max tax_yr quartile cht_quartile_size ;

/* compute the stayer quartile means */
%macro stayerquartilemeans;
	%do year=1996 %to 2014;

		/* get the means after computing the quartiles */
		proc sql;
			create table temp as
			select payer_tin_w2_max, tax_yr, quartile, mean(stayerwages&year.) as mean_quartile_stayerwages&year., count(*) as stayers_quartile_size&year. 
			from (select payer_tin_w2_max, tax_yr, quartile, stayer&year., stayerwages&year. from stayertemp where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and stayer&year.=1 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;

		/* merge using the hash table */

		/* fill the hash table using an outer join hash */
		data stayermeans;
			merge stayermeans temp;
			by payer_tin_w2_max tax_yr quartile;
		run;

		/* delete the temp table */
		proc sql;
			drop table temp;
		quit;

	%end;
%mend;
%stayerquartilemeans;

/* export the data sets */
data home.mean_quartile_stayerwages;
	set stayermeans;
run;

proc export data=stayermeans outfile="&csvdir./mean_quartile_stayerwages.csv" dbms=csv replace;
run;

/* can drop stayertemp now */
proc sql;
	drop table stayertemp;
quit;

/* now the cohort separation rate */
%let seprate1vars=
payer_tin_w2_max, tax_yr, quartile, cht_quartile_size, 
stayers_quartile_size1996, 
stayers_quartile_size1997, 
stayers_quartile_size1998, 
stayers_quartile_size1999, 
stayers_quartile_size2000, 
stayers_quartile_size2001, 
stayers_quartile_size2002, 
stayers_quartile_size2003, 
stayers_quartile_size2004, 
stayers_quartile_size2005, 
stayers_quartile_size2006, 
stayers_quartile_size2007, 
stayers_quartile_size2008, 
stayers_quartile_size2009, 
stayers_quartile_size2010, 
stayers_quartile_size2011, 
stayers_quartile_size2012, 
stayers_quartile_size2013, 
stayers_quartile_size2014;

proc sql;
	create table seprate1 as 
	select &seprate1vars.
	from stayermeans;
quit;

/* can drop stayermeans now */
proc sql;
	drop table stayermeans;
quit;

/* compute the separation rate using the cohort definition */
%macro chtseprate;
	data seprate1;
		set seprate1;
		%do year=1996 %to 2014;
			cht_quartile_sep_rate&year. = 1 - (stayers_quartile_size&year./cht_quartile_size);
		%end;
	run;
%mend;
%chtseprate;

/* export the data set */
data home.quartile_sep_rate_cht;
	set seprate1;
run;

proc export data=seprate1 outfile="&csvdir./quartile_sep_rate_cht.csv" dbms=csv replace;
run;

/* can drop seprate1 now */
proc sql;
	drop table seprate1;
quit;

/* now move into the second definition of the separation rate */
/* vars we dont need at the beginning of the loop */
%let the96vars= 
wages 
wages1996 
wages1997 
wages1998 
wages1999 
wages2000 
wages2001 
wages2002 
wages2003 
wages2004 
wages2005 
wages2006 
wages2007 
wages2008 
wages2009 
wages2010 
wages2011 
wages2012 
wages2013 
wages2014 
payer_tin2014;

/* entrants loop */
%macro entrants;
	data quarts;
		set quarts;
		entrant = 0;
		drop &the96vars.;

		/* compute the lag terms and entrants by each year */
		%do year=1997 %to 2014;

			%let lagyear=%eval(&year.-1);

			/* entrant */
			if tax_yr = &year. then 
				do;
					if payer_tin_w2_max ne payer_tin&lagyear. and payer_tin_w2_max ne 0 and payer_tin_w2_max ne . then entrant = 1;
				end;

			/* delete the lagged year */
			drop payer_tin&lagyear.;

		%end;
	run;
%mend;
%entrants;

/* now generate the second separation rate */
proc sql;
	create table seprate2 as
	select payer_tin_w2_max, tax_yr, quartile, sum(entrant) as quartile_entrants, count(*) as cht_quartile_size 
	from (select payer_tin_w2_max, tax_yr, quartile, entrant 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;

/* can delete quarts now */
proc sql;
	drop table quarts;
quit;

/* run the lag programs for the last year's cohort */
proc sql;
	create table lagyears as
	select payer_tin_w2_max, (tax_yr+1) as tax_yr, quartile, cht_quartile_size as cht_quartile_size_lag
	from seprate2
	where tax_yr<2014
	order by payer_tin_w2_max, tax_yr, quartile;
quit;

/* merge the lagged cohort sizes onto the seprate2 data set using hash tables for performance */
data seprate2;
	merge seprate2 lagyears;
	by payer_tin_w2_max tax_yr quartile;
run;

/* can drop lagyears now */
proc sql;
	drop table lagyears;
quit;

/* compute the separation rate using the entrant definition */
data seprate2;
	set seprate2;
	quartile_separators = cht_quartile_size-cht_quartile_size_lag-quartile_entrants;
	entrant_quartile_sep_rate = (cht_quartile_size-cht_quartile_size_lag-quartile_entrants)/(cht_quartile_size_lag);
	if payer_tin_w2_max = 0 or payer_tin_w2_max = . then delete;
	drop cht_quartile_size cht_quartile_size_lag;
run;

/* export the data sets */
data home.quartile_sep_rate_entrant;
	set seprate2;
run;

proc export data=seprate2 outfile="&csvdir./quartile_sep_rate_entrant.csv" dbms=csv replace;
run;

/* can drop seprate2 now */
proc sql;
	drop table seprate2;
quit;
