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

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

/* get only the necessary variables to save on space */
%let fulldatavars=
tin, 
payer_tin_w2_max, 
tax_yr, 
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;

/*load the full data file*/
proc sql;
	create table sampledata as
	select &fulldatavars. from
	home.patent_eins_workers_bp_full;
quit;

/* import and sort inventor data */
data inv;
	set home.unique_inventor;
		inventor=1;
run;

/* import and sort gender data */
data gender;
	set home.gender;
run;

/* wage loop */
%macro wages;
	data sampledata;
		set sampledata;
		wages = .;
		%do year=1996 %to 2014;
			/* replace with missing if wages == 0 */
			if wages&year. = 0 then wages&year. = .;
			if payer_tin&year. = 0 then payer_tin&year. = .;
			if tax_yr = &year. then wages=wages&year.;
		%end;
	run;
%mend;
%wages;

/* get a data set for generating the quartiles */
proc sql;
	create table forquartloop as
	select * from sampledata 
	where tax_yr=2000;
quit;

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

/* merge all of the data onto the sampledata */
proc sql;
	create table quarts as 
	select a.*, b.p25_v, b.p50_v, b.p75_v, c.gnd_ind, d.inventor
	from sampledata a 
		left join basetins b 
			on (a.payer_tin_w2_max=b.payer_tin_w2_max and a.tax_yr=b.tax_yr)
		left join gender c 
			on (a.tin=c.tin)
		left join inv d
			on (a.tin=d.tin)
	order by payer_tin_w2_max, tax_yr;
quit;

/* drop the sample data */
proc sql;
	drop table sampledata;
quit;
/* drop the gender data */
proc sql;
	drop table gender;
quit;
/* drop the inventor data */
proc sql;
	drop table inv;
quit;
/* drop the quartile values data */
proc sql;
	drop table basetins;
quit;
/* drop the quartile values data */
proc sql;
	drop table forquartloop;
quit;
/* drop the quartile values data */
proc sql;
	drop table temp;
quit;

/* stayers loop */
%macro quartiles;
	data quarts;
		set quarts;

		/* replace inventor with 0 if missing */
		if inventor=. then inventor=0;

		/* first compute the quartiles */
		quartile = .;
		%do year=1996 %to 2014;
			if (p25_v ne .) and (wages ne 0 and wages ne .) and (tax_yr = &year.) 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;
		drop p25_v p50_v p75_v;

		/* compute wages for genders */
		male=0;
		if gnd_ind="M" then male=1;
		malewages=.;
		if male=1 then malewages=wages;
		female=0;
		if gnd_ind="F" then female=1;
		femalewages=.;
		if female=1 then femalewages=wages;

		/* compute wages for inventors */
		invwages=.;
		if inventor=1 then invwages=wages;

		/* compute wages for genders and inventors */
		male_inv=0;
		if male=1 and inventor=1 then male_inv=1;
		male_invwages=.;
		if male_inv=1 then male_invwages=wages;
		female_inv=0;
		if female=1 and inventor=1 then female_inv=1;
		female_invwages=.;
		if female_inv=1 then female_invwages=wages;

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

			%let lagyear1=%eval(&year.-1);
			%let lagyear2=%eval(&year.-2);
			%let lagyear3=%eval(&year.-3);

			/* entrant in past three years */
			if tax_yr = &year. and &year. >= 2001 then 
				do;
					if (payer_tin_w2_max ne payer_tin&lagyear1. or payer_tin_w2_max ne payer_tin&lagyear2. or payer_tin_w2_max ne payer_tin&lagyear3.) and (payer_tin_w2_max ne 0 and payer_tin_w2_max ne .) then entrant3 = 1;
				end;

		%end;

		/* compute wages for stayers in current year */
		%do year=1996 %to 2014;

			stayer&year.=0;
			if payer_tin_w2_max=payer_tin&year. then stayer&year.=1;
			drop payer_tin&year.;

		%end;

	run;
%mend;
%quartiles;

/* compute the female, male, and inventor wages by quartile */
proc sql; 
	create table gender_inventor_q as
	select payer_tin_w2_max, tax_yr, quartile, mean(wages) as mean_quartile_wages, count(*) as cht_quartile_size, mean(malewages) as mean_male_q_wages, sum(male) as quartile_males, mean(femalewages) as mean_female_q_wages, sum(female) as quartile_females, mean(invwages) as mean_inv_q_wages, sum(inventor) as quartile_invs, mean(male_invwages) as mean_male_inv_q_wages, sum(male_inv) as quartile_male_invs, mean(female_invwages) as mean_female_inv_q_wages, sum(female_inv) as quartile_female_invs
	from (select payer_tin_w2_max, tax_yr, quartile, wages, malewages, male, femalewages, female, invwages, inventor, male_invwages, male_inv, female_invwages, female_inv 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;

/* grab the values for the stayer variables */
proc sql;
	create table stayerbase as 
	select payer_tin_w2_max, tax_yr, quartile 
	from gender_inventor_q 
	order by payer_tin_w2_max, tax_yr, quartile;
quit;

/* compute variables that can be backed out from other information */
data gender_inventor_q;
	set gender_inventor_q;

	/* compute non-inventor wages from total */
	quartile_noninvs = cht_quartile_size-quartile_invs;
	mean_noninv_q_wages = ((mean_quartile_wages*cht_quartile_size)-(mean_inv_q_wages*quartile_invs))/mean_noninv_q_wages;

	/* compute male non-inventor wages from total */
	quartile_male_noninvs = quartile_males-quartile_male_invs;
	mean_male_noninv_q_wages = ((mean_male_q_wages*quartile_males)-(mean_male_inv_q_wages*quartile_male_invs))/quartile_male_noninvs;

	/* compute female non-inventor wages from total */
	quartile_female_noninvs = quartile_females-quartile_female_invs;
	mean_female_noninv_q_wages = ((mean_female_q_wages*quartile_females)-(mean_female_inv_q_wages*quartile_female_invs))/quartile_female_noninvs;

	/* drop unnecessary variables */
	drop mean_quartile_wages cht_quartile_size;

run;

/* export the male and female and inventors data set */
data home.quartile_gender_inventor;
	set gender_inventor_q;
run;

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

/* drop gender inventor quartile table */
proc sql;
	drop table gender_inventor_q;
quit;

/* compute the female, male, and inventor wages */
proc sql; 
	create table gender_inventor as
	select payer_tin_w2_max, tax_yr, mean(malewages) as mean_male_wages, sum(male) as males, mean(femalewages) as mean_female_wages, sum(female) as females, mean(male_invwages) as mean_male_inv_wages, sum(male_inv) as male_invs, mean(female_invwages) as mean_female_inv_wages, sum(female_inv) as female_invs
	from (select payer_tin_w2_max, tax_yr, malewages, male, femalewages, female, male_invwages, male_inv, female_invwages, female_inv 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;

/* compute variables that can be backed out from other information */
data gender_inventor;
	set gender_inventor;

	/* compute male non-inventor wages from total */
	male_noninvs = males-male_invs;
	mean_male_noninv_wages = ((mean_male_wages*males)-(mean_male_inv_wages*male_invs))/male_noninvs;

	/* compute female non-inventor wages from total */
	female_noninvs = females-female_invs;
	mean_female_noninv_wages = ((mean_female_wages*females)-(mean_female_inv_wages*female_invs))/female_noninvs;

	/* drop unnecessary variables */
	drop males mean_male_wages females mean_female_wages;

run;

/* export the male and female and inventors data set */
data home.gender_inventor;
	set gender_inventor;
run;

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

/* drop gender inventor quartile table */
proc sql;
	drop table gender_inventor;
quit;

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

		/* get the stayer means after computing the quartiles */
		proc sql;
			create table temp as
			select payer_tin_w2_max, tax_yr, quartile, mean(wages) as mean_styr&year._q_currwages, mean(wages&year.) as mean_styr&year._q_oldwages
			from (select payer_tin_w2_max, tax_yr, quartile, wages, wages&year., stayer&year. from quarts 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 onto base data set */
		data stayerbase;
			merge stayerbase temp;
			by payer_tin_w2_max tax_yr quartile;
		run;

		/* delete the temp table for the next iteration */
		proc sql;
			drop table temp;
		quit;

	%end;
%mend;
%stayerquartilemeans;

/* export the data sets */
data home.mean_quartile_stayer_currwages;
	set stayerbase;
run;

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

/* drop stayer data */
proc sql;
	drop table stayerbase;
quit;

/* Start entrants within past three years by quartile */
proc sql;
	create table entrants3 as
	select payer_tin_w2_max, tax_yr, quartile, mean(wages) as mean_entrant3_q_wages, sum(entrant3) as quartile_entrant3s
	from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and entrant3=1 and quartile<>.
	group by payer_tin_w2_max, tax_yr, quartile
	order by payer_tin_w2_max, tax_yr, quartile;
quit;

/* export the data sets */
data home.mean_quartile_entrant3s;
	set entrants3;
run;

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

/* drop entrants3 */
proc sql;
	drop table entrants3;
quit;

/* Start entrants within past three years pooled */
proc sql;
	create table entrants3 as
	select payer_tin_w2_max, tax_yr, mean(wages) as mean_entrant3_wages, sum(entrant3) as entrant3s
	from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and entrant3=1
	group by payer_tin_w2_max, tax_yr
	order by payer_tin_w2_max, tax_yr;
quit;

/* export the data sets */
data home.mean_entrant3s;
	set entrants3;
run;

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

