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

/* import full data */
proc sql;
  create table working_data as
  select tin, tax_yr, payer_tin_w2_max, gnd_ind, inventor, &tinswages. from home.quartile_variables_sorted;
quit;

/* compute all required variables */
%macro add_vars;
  data working_data;
    set working_data;

    /* generate non-inventor */
    if inventor=. then noninventor=1;

    /* gender */
    male=.;
    if gnd_ind="M" then male=1;
    female=.;
    if gnd_ind="F" then female=1;

    /* now find stayer wages / wage growth */
    wages=.;
    wagechange=.;
    %do k=1996 %TO 2014;

      /* we need lags and leads for the wage growth definitions */
      %let lag1=%eval(&k.-1);
      %let lag2=%eval(&k.-2);
      %let lead=%eval(&k.+1);

      /* generate a stayer indicator and their wages */
      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.;

      /* generate the wage change variable for the relevant observations */
      if tax_yr >= 1998 & tax_yr <= 2013 then
        do;
          if payer_tin&lag2.=payer_tin&lag1 & payer_tin&lag1.=payer_tin&k. & payer_tin&k.=payer_tin&lead. then
          do;
            if tax_yr = &k. then wagechange = (wages&k. - wages&lag1.);
          end;
          drop payer_tin&lag2. wages&lag2.;
        end;

    %end;

    drop payer_tin2012 payer_tin2013 payer_tin2014 wages2012 wages2013 wages2014 gnd_ind;

    if payer_tin_w2_max=0 or payer_tin_w2_max=. then delete;

  run;
%mend;
%add_vars;

/* stayer data */
%let stayervars=
sum(stayer1996) as stayers1996,
sum(stayer1997) as stayers1997,
sum(stayer1998) as stayers1998,
sum(stayer1999) as stayers1999,
sum(stayer2000) as stayers2000,
sum(stayer2001) as stayers2001,
sum(stayer2002) as stayers2002,
sum(stayer2003) as stayers2003,
sum(stayer2004) as stayers2004,
sum(stayer2005) as stayers2005,
sum(stayer2006) as stayers2006,
sum(stayer2007) as stayers2007,
sum(stayer2008) as stayers2008,
sum(stayer2009) as stayers2009,
sum(stayer2010) as stayers2010,
sum(stayer2011) as stayers2011,
sum(stayer2012) as stayers2012,
sum(stayer2013) as stayers2013,
sum(stayer2014) as stayers2014,
sum(stayer1996*male) as male_stayers1996,
sum(stayer1997*male) as male_stayers1997,
sum(stayer1998*male) as male_stayers1998,
sum(stayer1999*male) as male_stayers1999,
sum(stayer2000*male) as male_stayers2000,
sum(stayer2001*male) as male_stayers2001,
sum(stayer2002*male) as male_stayers2002,
sum(stayer2003*male) as male_stayers2003,
sum(stayer2004*male) as male_stayers2004,
sum(stayer2005*male) as male_stayers2005,
sum(stayer2006*male) as male_stayers2006,
sum(stayer2007*male) as male_stayers2007,
sum(stayer2008*male) as male_stayers2008,
sum(stayer2009*male) as male_stayers2009,
sum(stayer2010*male) as male_stayers2010,
sum(stayer2011*male) as male_stayers2011,
sum(stayer2012*male) as male_stayers2012,
sum(stayer2013*male) as male_stayers2013,
sum(stayer2014*male) as male_stayers2014,
mean(stayerwages1996*male) as male_stayer_wages1996,
mean(stayerwages1997*male) as male_stayer_wages1997,
mean(stayerwages1998*male) as male_stayer_wages1998,
mean(stayerwages1999*male) as male_stayer_wages1999,
mean(stayerwages2000*male) as male_stayer_wages2000,
mean(stayerwages2001*male) as male_stayer_wages2001,
mean(stayerwages2002*male) as male_stayer_wages2002,
mean(stayerwages2003*male) as male_stayer_wages2003,
mean(stayerwages2004*male) as male_stayer_wages2004,
mean(stayerwages2005*male) as male_stayer_wages2005,
mean(stayerwages2006*male) as male_stayer_wages2006,
mean(stayerwages2007*male) as male_stayer_wages2007,
mean(stayerwages2008*male) as male_stayer_wages2008,
mean(stayerwages2009*male) as male_stayer_wages2009,
mean(stayerwages2010*male) as male_stayer_wages2010,
mean(stayerwages2011*male) as male_stayer_wages2011,
mean(stayerwages2012*male) as male_stayer_wages2012,
mean(stayerwages2013*male) as male_stayer_wages2013,
mean(stayerwages2014*male) as male_stayer_wages2014,
sum(stayer1996*female) as female_stayers1996,
sum(stayer1997*female) as female_stayers1997,
sum(stayer1998*female) as female_stayers1998,
sum(stayer1999*female) as female_stayers1999,
sum(stayer2000*female) as female_stayers2000,
sum(stayer2001*female) as female_stayers2001,
sum(stayer2002*female) as female_stayers2002,
sum(stayer2003*female) as female_stayers2003,
sum(stayer2004*female) as female_stayers2004,
sum(stayer2005*female) as female_stayers2005,
sum(stayer2006*female) as female_stayers2006,
sum(stayer2007*female) as female_stayers2007,
sum(stayer2008*female) as female_stayers2008,
sum(stayer2009*female) as female_stayers2009,
sum(stayer2010*female) as female_stayers2010,
sum(stayer2011*female) as female_stayers2011,
sum(stayer2012*female) as female_stayers2012,
sum(stayer2013*female) as female_stayers2013,
sum(stayer2014*female) as female_stayers2014,
mean(stayerwages1996*female) as female_stayer_wages1996,
mean(stayerwages1997*female) as female_stayer_wages1997,
mean(stayerwages1998*female) as female_stayer_wages1998,
mean(stayerwages1999*female) as female_stayer_wages1999,
mean(stayerwages2000*female) as female_stayer_wages2000,
mean(stayerwages2001*female) as female_stayer_wages2001,
mean(stayerwages2002*female) as female_stayer_wages2002,
mean(stayerwages2003*female) as female_stayer_wages2003,
mean(stayerwages2004*female) as female_stayer_wages2004,
mean(stayerwages2005*female) as female_stayer_wages2005,
mean(stayerwages2006*female) as female_stayer_wages2006,
mean(stayerwages2007*female) as female_stayer_wages2007,
mean(stayerwages2008*female) as female_stayer_wages2008,
mean(stayerwages2009*female) as female_stayer_wages2009,
mean(stayerwages2010*female) as female_stayer_wages2010,
mean(stayerwages2011*female) as female_stayer_wages2011,
mean(stayerwages2012*female) as female_stayer_wages2012,
mean(stayerwages2013*female) as female_stayer_wages2013,
mean(stayerwages2014*female) as female_stayer_wages2014,
sum(stayer1996*inventor) as inventor_stayers1996,
sum(stayer1997*inventor) as inventor_stayers1997,
sum(stayer1998*inventor) as inventor_stayers1998,
sum(stayer1999*inventor) as inventor_stayers1999,
sum(stayer2000*inventor) as inventor_stayers2000,
sum(stayer2001*inventor) as inventor_stayers2001,
sum(stayer2002*inventor) as inventor_stayers2002,
sum(stayer2003*inventor) as inventor_stayers2003,
sum(stayer2004*inventor) as inventor_stayers2004,
sum(stayer2005*inventor) as inventor_stayers2005,
sum(stayer2006*inventor) as inventor_stayers2006,
sum(stayer2007*inventor) as inventor_stayers2007,
sum(stayer2008*inventor) as inventor_stayers2008,
sum(stayer2009*inventor) as inventor_stayers2009,
sum(stayer2010*inventor) as inventor_stayers2010,
sum(stayer2011*inventor) as inventor_stayers2011,
sum(stayer2012*inventor) as inventor_stayers2012,
sum(stayer2013*inventor) as inventor_stayers2013,
sum(stayer2014*inventor) as inventor_stayers2014,
mean(stayerwages1996*inventor) as inventor_stayer_wages1996,
mean(stayerwages1997*inventor) as inventor_stayer_wages1997,
mean(stayerwages1998*inventor) as inventor_stayer_wages1998,
mean(stayerwages1999*inventor) as inventor_stayer_wages1999,
mean(stayerwages2000*inventor) as inventor_stayer_wages2000,
mean(stayerwages2001*inventor) as inventor_stayer_wages2001,
mean(stayerwages2002*inventor) as inventor_stayer_wages2002,
mean(stayerwages2003*inventor) as inventor_stayer_wages2003,
mean(stayerwages2004*inventor) as inventor_stayer_wages2004,
mean(stayerwages2005*inventor) as inventor_stayer_wages2005,
mean(stayerwages2006*inventor) as inventor_stayer_wages2006,
mean(stayerwages2007*inventor) as inventor_stayer_wages2007,
mean(stayerwages2008*inventor) as inventor_stayer_wages2008,
mean(stayerwages2009*inventor) as inventor_stayer_wages2009,
mean(stayerwages2010*inventor) as inventor_stayer_wages2010,
mean(stayerwages2011*inventor) as inventor_stayer_wages2011,
mean(stayerwages2012*inventor) as inventor_stayer_wages2012,
mean(stayerwages2013*inventor) as inventor_stayer_wages2013,
mean(stayerwages2014*inventor) as inventor_stayer_wages2014,
sum(stayer1996*noninventor) as noninventor_stayers1996,
sum(stayer1997*noninventor) as noninventor_stayers1997,
sum(stayer1998*noninventor) as noninventor_stayers1998,
sum(stayer1999*noninventor) as noninventor_stayers1999,
sum(stayer2000*noninventor) as noninventor_stayers2000,
sum(stayer2001*noninventor) as noninventor_stayers2001,
sum(stayer2002*noninventor) as noninventor_stayers2002,
sum(stayer2003*noninventor) as noninventor_stayers2003,
sum(stayer2004*noninventor) as noninventor_stayers2004,
sum(stayer2005*noninventor) as noninventor_stayers2005,
sum(stayer2006*noninventor) as noninventor_stayers2006,
sum(stayer2007*noninventor) as noninventor_stayers2007,
sum(stayer2008*noninventor) as noninventor_stayers2008,
sum(stayer2009*noninventor) as noninventor_stayers2009,
sum(stayer2010*noninventor) as noninventor_stayers2010,
sum(stayer2011*noninventor) as noninventor_stayers2011,
sum(stayer2012*noninventor) as noninventor_stayers2012,
sum(stayer2013*noninventor) as noninventor_stayers2013,
sum(stayer2014*noninventor) as noninventor_stayers2014,
mean(stayerwages1996*noninventor) as noninventor_stayer_wages1996,
mean(stayerwages1997*noninventor) as noninventor_stayer_wages1997,
mean(stayerwages1998*noninventor) as noninventor_stayer_wages1998,
mean(stayerwages1999*noninventor) as noninventor_stayer_wages1999,
mean(stayerwages2000*noninventor) as noninventor_stayer_wages2000,
mean(stayerwages2001*noninventor) as noninventor_stayer_wages2001,
mean(stayerwages2002*noninventor) as noninventor_stayer_wages2002,
mean(stayerwages2003*noninventor) as noninventor_stayer_wages2003,
mean(stayerwages2004*noninventor) as noninventor_stayer_wages2004,
mean(stayerwages2005*noninventor) as noninventor_stayer_wages2005,
mean(stayerwages2006*noninventor) as noninventor_stayer_wages2006,
mean(stayerwages2007*noninventor) as noninventor_stayer_wages2007,
mean(stayerwages2008*noninventor) as noninventor_stayer_wages2008,
mean(stayerwages2009*noninventor) as noninventor_stayer_wages2009,
mean(stayerwages2010*noninventor) as noninventor_stayer_wages2010,
mean(stayerwages2011*noninventor) as noninventor_stayer_wages2011,
mean(stayerwages2012*noninventor) as noninventor_stayer_wages2012,
mean(stayerwages2013*noninventor) as noninventor_stayer_wages2013,
mean(stayerwages2014*noninventor) as noninventor_stayer_wages2014;

/* save the new stayer variables data */
proc sql;
  create table stayers as
    select payer_tin_w2_max, tax_yr, &stayervars from working_data
    group by payer_tin_w2_max, tax_yr;
quit;

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

/* get the wage growth data */
proc sql;
  create table wage_growth as
    select payer_tin_w2_max, tax_yr,
        sum(wagechange <> .) as cont_wrkrs, mean(wagechange) as wage_growth,
        sum(wagechange <> . and male=1) as cont_males, mean(wagechange*male) as male_wage_growth,
        sum(wagechange <> . and female=1) as cont_females, mean(wagechange*female) as female_wage_growth,
        sum(wagechange <> . and inventor=1) as cont_inventors, mean(wagechange*inventor) as inventor_wage_growth,
        sum(wagechange <> . and noninventor=1) as cont_noninventors, mean(wagechange*noninventor) as noninventor_wage_growth
    from working_data group by payer_tin_w2_max, tax_yr;
quit;

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