
%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=
x.tin,
x.tax_yr,
x.payer_tin_w2_max,
x.gnd_ind,
x.payer_tin1996,
x.payer_tin1997,
x.payer_tin1998,
x.payer_tin1999,
x.payer_tin2000,
x.payer_tin2001,
x.payer_tin2002,
x.payer_tin2003,
x.payer_tin2004,
x.payer_tin2005,
x.payer_tin2006,
x.payer_tin2007,
x.payer_tin2008,
x.payer_tin2009,
x.payer_tin2010,
x.payer_tin2011,
x.payer_tin2012,
x.payer_tin2013,
x.payer_tin2014;

* 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 occupation information to each person in the final data;
proc sql;
    create table final_sample as
      select l.*, r.applicationyear
      from
        (select &want_vars. from
          home.quartile_variables_sorted as x
          inner join
          (select distinct a.tin from
            home.quartile_variables_sorted as a
            inner join
            (select tin from taad) as b
            on a.payer_tin_w2_max=b.tin
          ) as y
          on x.tin=y.tin
        ) as l
        left join
        taad as r
        on l.payer_tin_w2_max=r.tin
        group by l.tin having max(~missing(r.applicationyear))=1;

    create table final_sample_w_occ as
      select * from
      final_sample as a
      left join
      (select x.tin, y.prim_occ_ID, y.mean_occ_wages_2014 from
        (select tin, prim_occ from home.occ) as x
        inner join
        (select prim_occ, prim_occ_ID, mean_occ_wages_2014 from home.prim_occ_xwalk_final) as y
        on x.prim_occ=y.prim_occ
      ) as b
      on a.tin = b.tin;

    drop table final_sample;

  quit;

* if some occupations are still missing wages from the random sample, we will fill in from the missing values;
data _null_;
  set home.prim_occ_xwalk_final(keep=prim_occ mean_occ_wages_2014);
  if prim_occ = "" then do;
    call symput("miss_mean_wage",mean_occ_wages_2014);
  end;
run;

* determine stayer status for each tin-tax_yr observation given the application years of the firms;
%macro wagetin;
  data final_sample_w_occ(keep=payer_tin_w2_max tax_yr male female stayer mean_occ_wages_2014);
    set final_sample_w_occ;

    /* overwrite missing values with the mean wage value of missings */
    if mean_occ_wages_2014 = . then mean_occ_wages_2014 = &miss_mean_wage.;

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

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

%mend;

%wagetin;

* compute the means and counts;
proc sql;
    create table mean_occ_wages as
      select a.* from
        (select payer_tin_w2_max, tax_yr,
                mean(mean_occ_wages_2014) as occ_wages,
                mean(male*mean_occ_wages_2014) as male_occ_wages,
                mean(female*mean_occ_wages_2014) as female_occ_wages,
                mean(stayer*mean_occ_wages_2014) as styr_occ_wages,
                mean(male*stayer*mean_occ_wages_2014) as male_styr_occ_wages,
                mean(female*stayer*mean_occ_wages_2014) as female_styr_occ_wages
        from
            (select * from final_sample_w_occ
                  where payer_tin_w2_max <> 0
                    and payer_tin_w2_max <> .
                    and mean_occ_wages_2014 <> .
            )
            group by payer_tin_w2_max, tax_yr
        ) as a
        inner join
        taad as b
        on a.payer_tin_w2_max=b.tin;
  quit;

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

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