/* 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;

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

		%do year=1996 %to 2014;

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

			/* 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.;

			/* fill in pre wages based on year */
			if tax_yr = &year. and &year. >= 1997 then prewages = wages&lagyear.;

			/* fill in next wages based on year */
			if tax_yr = &year. and &year. <= 2013 then nxtwages = wages&nxtyear.;

		%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;

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

%let unnecvars2= 
wages 
prewages 
nxtwages 
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;

/* entrants loop */
%macro entrants;
	data quarts;
		set quarts;

		/* first compute the quartiles */
		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;

		/* then do the leavers and entrants */
		entrant = 0;
		separator = 0;
		drop &unnecvars1.;

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

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

			/* entrant */
			if tax_yr = &year. and &year. >= 1997 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;

			/* separator */
			if tax_yr = &year. and &year. <= 2013 then 
				do;
					if payer_tin_w2_max ne payer_tin&nxtyear. and payer_tin_w2_max ne 0 and payer_tin_w2_max ne . then separator = 1;
				end;

		%end;

		/* get the entrant and pre-entrant wages */
		entrantwages = .;
		entrantprewages = .;
		if entrant = 1 then
			do;
				entrantwages=wages;
				entrantprewages=prewages;
				if entrantprewages = . then entrantprewages = 0;
			end;

		/* get the separator wages */
		separatorwages = .;
		separatornextwages = .;
		if separator = 1 then 
			do;
				separatorwages=wages;
				separatornextwages=nxtwages;
				if separatornextwages = . then separatornextwages = 0;
			end;

		/* clean up */
		drop &unnecvars2.;
	run;
%mend;
%entrants;

/* now generate the number of entrants */
proc sql;
	create table entrants as
	select payer_tin_w2_max, tax_yr, quartile, sum(entrant) as quartile_entrants, mean(entrantwages) as quartile_mean_entrantwages, mean(entrantprewages) as quartile_mean_entrantlagwages
	from (select payer_tin_w2_max, tax_yr, quartile, entrant, entrantwages, entrantprewages 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;

/* now generate the number of separators */
proc sql;
	create table separators as
	select payer_tin_w2_max, tax_yr+1 as tax_yr, quartile, sum(separator) as quartile_separators, mean(separatorwages) as quartile_mean_separatorwages, mean(separatornextwages) as quartile_mean_separatorleadwages, count(*) as emp_quartile_lag
	from (select payer_tin_w2_max, tax_yr, quartile, separator, separatorwages, separatornextwages from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and quartile<>. and tax_yr<=2013) as b
	group by b.payer_tin_w2_max, b.tax_yr, b.quartile
	order by payer_tin_w2_max, tax_yr, quartile;
quit;

/* pooled, nonquartile separators */
proc sql;
	create table separators_pooled as
	select payer_tin_w2_max, tax_yr+1 as tax_yr, sum(separator) as separators, mean(separatorwages) as mean_separatorwages, mean(separatornextwages) as mean_separatorleadwages
	from (select payer_tin_w2_max, tax_yr, separator, separatorwages, separatornextwages from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and tax_yr<=2013) as b
	group by b.payer_tin_w2_max, b.tax_yr
	order by payer_tin_w2_max, tax_yr;
quit;

/* export the data sets */
data home.separators_pooled;
	set separators_pooled;
run;

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

/* don't need quarts or separators pooled anymore */
proc sql;
	drop table quarts;
quit;

proc sql;
	drop table separators_pooled;
quit;

/* merge entrants and separators and compute the separation rate */
data seprate2;
	merge entrants separators;
	by payer_tin_w2_max tax_yr quartile;
	quartile_sep_rate = quartile_separators/emp_quartile_lag;
	if payer_tin_w2_max = 0 or payer_tin_w2_max = . then delete;
	drop emp_quartile_lag;
run;

/* don't need entrants or separators anymore */
proc sql;
	drop table entrants;
quit;

proc sql;
	drop table separators;
quit;

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

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