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

/* append all of the by year columns together */
data home.quartile_variables_sorted;
	set home.quartile_variables_sorted_1996 home.quartile_variables_sorted_1997 home.quartile_variables_sorted_1998 home.quartile_variables_sorted_1999 home.quartile_variables_sorted_2000 home.quartile_variables_sorted_2001 home.quartile_variables_sorted_2002 home.quartile_variables_sorted_2003 home.quartile_variables_sorted_2004 home.quartile_variables_sorted_2005 home.quartile_variables_sorted_2006 home.quartile_variables_sorted_2007 home.quartile_variables_sorted_2008 home.quartile_variables_sorted_2009 home.quartile_variables_sorted_2010 home.quartile_variables_sorted_2011 home.quartile_variables_sorted_2012 home.quartile_variables_sorted_2013 home.quartile_variables_sorted_2014;
run;

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

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

		/* create indicator for non missing attend_college */
		newvar=attend_college*1;
		drop attend_college;
		rename newvar=attend_college;
		nonmiss_college_ind = 1;
		if attend_college = . then nonmiss_college_ind=0;

		/* create indicator for non missing tentative taxation */
		nonmiss_tent_tx = 1;
		if tent_tx = . then nonmiss_tent_tx=0;

		/* get the age for each person in each year */
		age = tax_yr-ssa_yob;

		/* compute wages for age groups (<=40 and >40) */
		/* create an indicator */
		over40 = 0;
		under40 = 0;
		if age<=40 and age ne . then under40 = 1;
		if age>40 and age ne . then over40 = 1;
		/* 40 and under */
		under40wages=.;
		if under40=1 then under40wages=wages;
		/* 40 and over */
		over40wages=.;
		if over40=1 then over40wages=wages;

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

/* cohort variables loop */
%let cht_varlist = over40 under40 male female inventor noninventor;
%macro chtloop;

	%local i next_name;
	%do i=1 %to %sysfunc(countw(&cht_varlist));

		/* dequeue next variable name */
		%let next_name = %scan(&cht_varlist, &i);

		/* pooled cohort variables: input variables */
		%let inp_pooledvars=
		payer_tin_w2_max,
		tax_yr,
		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,
		&next_name.;

		/* pooled cohort variables: output variables */
		%let outp_pooledvars=
		payer_tin_w2_max,
		tax_yr,
		mean(chtwages_1996) as &next_name._chtwages_1996,
		mean(chtwages_1997) as &next_name._chtwages_1997,
		mean(chtwages_1998) as &next_name._chtwages_1998,
		mean(chtwages_1999) as &next_name._chtwages_1999,
		mean(chtwages_2000) as &next_name._chtwages_2000,
		mean(chtwages_2001) as &next_name._chtwages_2001,
		mean(chtwages_2002) as &next_name._chtwages_2002,
		mean(chtwages_2003) as &next_name._chtwages_2003,
		mean(chtwages_2004) as &next_name._chtwages_2004,
		mean(chtwages_2005) as &next_name._chtwages_2005,
		mean(chtwages_2006) as &next_name._chtwages_2006,
		mean(chtwages_2007) as &next_name._chtwages_2007,
		mean(chtwages_2008) as &next_name._chtwages_2008,
		mean(chtwages_2009) as &next_name._chtwages_2009,
		mean(chtwages_2010) as &next_name._chtwages_2010,
		mean(chtwages_2011) as &next_name._chtwages_2011,
		mean(chtwages_2012) as &next_name._chtwages_2012,
		mean(chtwages_2013) as &next_name._chtwages_2013,
		mean(chtwages_2014) as &next_name._chtwages_2014,
		sum(&next_name.) as cht_&next_name.s;

		/* compute means and save data set */
		proc sql;
			create table cht&next_name. as
			select &outp_pooledvars.
			from (select &inp_pooledvars.
				from quarts where payer_tin_w2_max<>0 and payer_tin_w2_max<>. and &next_name.=1) as b
			group by b.payer_tin_w2_max, b.tax_yr
			order by payer_tin_w2_max, tax_yr;
		quit;

		proc export data=cht&next_name. outfile="&csvdir./cohort_&next_name._wages.csv" dbms=csv replace;
		run;

		/* drop the table */
		proc sql;
			drop table cht&next_name.;
		quit;

	%end;

%mend;
%chtloop;

/* other pooled variables: input variables*/
%let inp_age_vars=
payer_tin_w2_max,
tax_yr,
age,
over40,
under40,
over40wages,
under40wages,
attend_college,
nonmiss_college_ind,
tent_tx,
nonmiss_tent_tx;

/* other pooled variables: output variables */
%let outp_age_vars=
payer_tin_w2_max,
tax_yr,
mean(age) as mean_age,
sum(over40) as over40s,
sum(under40) as under40s,
mean(over40wages) as mean_over40wages,
mean(under40wages) as mean_under40wages,
mean(attend_college) as share_college,
sum(nonmiss_college_ind) as college_reported,
mean(tent_tx) as mean_tentative_taxes,
sum(nonmiss_tent_tx) as tentative_taxes_reported;

/* create the databank output data set */
proc sql;
	create table firm_ages as
	select &outp_age_vars.
	from
	(select &inp_age_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=firm_ages outfile="&csvdir./firm_ages_college_tenttax.csv" dbms=csv replace;
run;

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