
%let home= %str(wherever quartile_variables_sorted is located);
libname home 'wherever quartile_variables_sorted is located';
%let decyr= %str(/user/patents/);
libname decyr '/user/patents/';
options obs=max;

* variables we need from the QVS data set;
%let want_vars=
l.tin,
l.tax_yr,
l.payer_tin_w2_max,
l.gnd_ind,
l.inventor,
l.wages1996,
l.wages1997,
l.wages1998,
l.wages1999,
l.wages2000,
l.wages2001,
l.wages2002,
l.wages2003,
l.wages2004,
l.wages2005,
l.wages2006,
l.wages2007,
l.wages2008,
l.wages2009,
l.wages2010,
l.wages2011,
l.wages2012,
l.wages2013,
l.wages2014,
l.payer_tin1996,
l.payer_tin1997,
l.payer_tin1998,
l.payer_tin1999,
l.payer_tin2000,
l.payer_tin2001,
l.payer_tin2002,
l.payer_tin2003,
l.payer_tin2004,
l.payer_tin2005,
l.payer_tin2006,
l.payer_tin2007,
l.payer_tin2008,
l.payer_tin2009,
l.payer_tin2010,
l.payer_tin2011,
l.payer_tin2012,
l.payer_tin2013,
l.payer_tin2014;

* get a data set with the minimum wage equivalents;
proc import datafile="&home./fedminwage.csv" out=fmw dbms=csv replace;
    getnames=yes;
  run;

* get a data set of all of the workers with whom we need to concern ourselves;
proc import datafile="&decyr./tin_appnum_appyear_decyear.csv" out=taad dbms=csv replace;
    getnames=yes;
  run;

* delete from the data any firms with decision years after 2014;
data taad;
  set taad;
    if decisionyear>2014 then delete;
    else output;
run;

* now attach the application numbers to the firms;
proc sql;
    create table final_sample as
    select a.*, b.equiv from
    (select &want_vars, r.applicationyear
      from home.quartile_variables_sorted as l
      inner join
      taad as r
      on l.payer_tin_w2_max=r.tin
    ) as a
    left join
    fmw as b
    on a.tax_yr=b.tax_yr;
  quit;

* determine inventor wages;
%macro wagetin;
  data final_sample;
    set final_sample;

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

    /* assign inventor */
    if inventor ne 1 then inventor = .;
    noninventor = .;
    if inventor ne 1 then noninventor = 1;

    /* assign wages in each year */
    wages = .;
    %do y = 1996 %to 2014;
      if tax_yr=&y. then wages = wages&y.;
    %end;

    /* figure out stayer information based on application year */
    cht=0;
    stayer=.;
    if applicationyear ne . then do;
      %do i = 2000 %to 2010;
        if applicationyear=&i. then do;
          if payer_tin_w2_max=payer_tin&i. then stayer=1;
          if tax_yr=&i. then cht=1;
        end;
      %end;
    end;

    /* figure out entrants and within 3-year entrants */
    ent = .;
    ent3 = .;
    %do i = 2001 %to 2014;
      if tax_yr=&i. then do;
        %let im1 = %eval(&i.-1);
        %let im2 = %eval(&i.-2);
        %let im3 = %eval(&i.-3);
        if payer_tin_w2_max ne payer_tin&im1. then do;
          ent = 1;
          ent3 = 1;
        end;
        else if &i.>=2003 and (payer_tin_w2_max ne payer_tin&im2. or payer_tin_w2_max ne payer_tin&im3.)
          then ent3 = 1;
      end;
    %end;

    /* drop workers with less than the minimum wage equivalent */
    if wages<equiv or payer_tin_w2_max=0 or payer_tin_w2_max=. or wages=. then delete;

    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;

%mend;

%wagetin;

/* compute the quartiles based on FTE equivalent wages*/
proc means data=final_sample noprint nway;
	class payer_tin_w2_max tax_yr;
	var wages;
	output out=fte_qs(keep=payer_tin_w2_max tax_yr _freq_ p25_v p50_v p75_v) p25=p25_v p50=p50_v p75=p75_v;
run;

proc sql;

    /* add on the quartiles to the data set with the firms in question */
    create table final_sample_w_qs as
      select l.*, r.*,
             case when wages <= p25_v then 1
                  when p25_v < wages and wages <= p50_v then 2
                  when p50_v < wages and wages <= p75_v then 3
                  else 4
              end as fte_quartile
      from
      final_sample as l
      inner join
      (select payer_tin_w2_max, tax_yr, p25_v, p50_v, p75_v
          from fte_qs where _freq_>=4 and ~missing(p25_v) and ~missing(p50_v) and ~missing(p75_v)
      ) as r
      on l.payer_tin_w2_max=r.payer_tin_w2_max and l.tax_yr=r.tax_yr;

    /* determine the quartiles outcome measures */
    create table q_outcomes as
    select l.payer_tin_w2_max, l.tax_yr, l.fte_quartile, l.emps_fte, l.wages_fte,
           coalesce(r.styrs_fte,0) as styrs_fte, r.styr_wages_fte
    from
      (
        select payer_tin_w2_max, tax_yr, fte_quartile, count(*) as emps_fte, mean(wages) as wages_fte
        from
          final_sample_w_qs
          group by payer_tin_w2_max, tax_yr, fte_quartile
      ) as l
      left join
      (
        select a.payer_tin_w2_max, a.tax_yr, b.fte_quartile, mean(a.wages) as styr_wages_fte, count(*) as styrs_fte
        from
          final_sample_w_qs as a
          inner join
          (select tin, payer_tin_w2_max, fte_quartile from final_sample_w_qs where cht=1) as b
          on a.tin=b.tin and a.payer_tin_w2_max=b.payer_tin_w2_max
          group by a.payer_tin_w2_max, a.tax_yr, b.fte_quartile
      ) as r
      on l.payer_tin_w2_max=r.payer_tin_w2_max and
         l.tax_yr=r.tax_yr and
         l.fte_quartile=r.fte_quartile;

    /* determine the entrant outcome measures */
    create table e_outcomes as
    select payer_tin_w2_max, tax_yr, ents_fte, ents_wages_fte,
    case
      when tax_yr < 2003 then . else ent3s_fte
    end as ent3s_fte,
    case
      when tax_yr < 2003 then . else ent3s_wages_fte
    end as ent3s_wages_fte
    from
      (select payer_tin_w2_max, tax_yr,  sum(ent=1) as  ents_fte,  mean(wages*ent) as  ents_wages_fte,
                                        sum(ent3=1) as ent3s_fte, mean(wages*ent3) as ent3s_wages_fte
        from final_sample_w_qs where tax_yr >= 2001 group by payer_tin_w2_max, tax_yr
      );
  quit;

proc export data=q_outcomes outfile="&home./FTE_quartile_outcomes.csv" dbms=csv replace;
run;

proc export data=e_outcomes outfile="&home./FTE_entrant_outcomes.csv" dbms=csv replace;
run;
