
libname inv '/user/patents/build/sas/';
%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.wages1996,
x.wages1997,
x.wages1998,
x.wages1999,
x.wages2000,
x.wages2001,
x.wages2002,
x.wages2003,
x.wages2004,
x.wages2005,
x.wages2006,
x.wages2007,
x.wages2008,
x.wages2009,
x.wages2010,
x.wages2011,
x.wages2012,
x.wages2013,
x.wages2014;

* 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 l.*, r.applicationyear, r.appnum, r.decisionyear
      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_invs as
      select * from
      final_sample as a
      left join
      (select distinct tin, serial_number as appnum, 1 as inv_on_app from inv.app_inventor) as b
      on a.tin=b.tin and a.appnum=b.appnum;
  quit;

* determine inventor wages;
%macro wagetin;
  data final_sample_w_invs(keep=payer_tin_w2_max tax_yr inv_on_app wages);
    set final_sample_w_invs;

    wages = .;
    %do y = 1996 %to 2014;
      if tax_yr=&y. then wages = wages&y.;
    %end;

    if payer_tin_w2_max = . or payer_tin_w2_max = 0 or wages = . or wages = 0 then delete;
    else output;
  run;
%mend;
%wagetin;

* calculate number of listed iventors and their mean wages at the firm;
proc sql;
    create table inv_outcomes as
      select a.* from
        (select payer_tin_w2_max, tax_yr, coalesce(sum(inv_on_app),0) as pinv, mean(inv_on_app*wages) as wages_pinv
          from final_sample_w_invs group by payer_tin_w2_max, tax_yr
        ) as a
        inner join
        taad as b
        on a.payer_tin_w2_max=b.tin
        order by a.payer_tin_w2_max, a.tax_yr;
  quit;

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