/* directory with quartile_variables_sorted.sas7bdat */
%let home= %str(/user/patents/);
libname home '/user/patents/';
/* directory with tin_appnum_appyear_decyear.csv */
%let decyr= %str(/user/patents/);
libname decyr '/user/patents/';
/* directory with output CSV files */
%let csvdir= %str(/user/patents/build/sas/);
libname csvdir '/user/patents/build/sas/';

options obs=max;


* pull in the application year data;
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;


* process the quartile_variables_sorted data set, then merge on the application information;
proc sql;
    create table final_sample as
    select
      /* worker information, as is */
      a.tin, a.tax_yr, a.payer_tin_w2_max, a.inventor,
      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,
      /* making life easier with structure of median computation below */
      1 as pooled,
      /* processed worker information */
            /* male indicator */
            case
              when a.gnd_ind='M' then 1
                                 else .
            end as male,
            /* female indicator */
            case
              when a.gnd_ind='F' then 1
                                 else .
            end as female,
            /* noninventor indicator */
            case
              when a.inventor=1  then .
                                 else 1
            end as noninventor,
      /* application information */
      b.appnum, b.applicationyear, b.decisionyear,
      /* consolidated wage information */
      case
        when a.tax_yr=1996 then coalesce(a.wages1996,0)
        when a.tax_yr=1997 then coalesce(a.wages1997,0)
        when a.tax_yr=1998 then coalesce(a.wages1998,0)
        when a.tax_yr=1999 then coalesce(a.wages1999,0)
        when a.tax_yr=2000 then coalesce(a.wages2000,0)
        when a.tax_yr=2001 then coalesce(a.wages2001,0)
        when a.tax_yr=2002 then coalesce(a.wages2002,0)
        when a.tax_yr=2003 then coalesce(a.wages2003,0)
        when a.tax_yr=2004 then coalesce(a.wages2004,0)
        when a.tax_yr=2005 then coalesce(a.wages2005,0)
        when a.tax_yr=2006 then coalesce(a.wages2006,0)
        when a.tax_yr=2007 then coalesce(a.wages2007,0)
        when a.tax_yr=2008 then coalesce(a.wages2008,0)
        when a.tax_yr=2009 then coalesce(a.wages2009,0)
        when a.tax_yr=2010 then coalesce(a.wages2010,0)
        when a.tax_yr=2011 then coalesce(a.wages2011,0)
        when a.tax_yr=2012 then coalesce(a.wages2012,0)
        when a.tax_yr=2013 then coalesce(a.wages2013,0)
                           else coalesce(a.wages2014,0)
      end as wages
    from
      home.quartile_variables_sorted as a
      left join
      taad as b
      on a.payer_tin_w2_max = b.tin;

    /* create a data set for the application year cohorts (one worker can be in multiple cohorts) */
    create table cohorts as
    select a.tin, a.tax_yr, b.payer_tin_w2_max, b.applicationyear, a.wages, b.appyr_wages
    from
      final_sample as a
      inner join
      (
        select tin, applicationyear, payer_tin_w2_max, wages as appyr_wages
        from final_sample
        where applicationyear=tax_yr
      ) as b
      on a.tin=b.tin;

  quit;

* determine the stayer status of the workers;
%macro stayers;
  data final_sample(drop=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);
    set final_sample;

      /* figure out stayer information based on application year (delete observations without app year)*/
      if applicationyear ne . then do;
          stayer=.;
          %do i = 2000 %to 2010;
            if applicationyear=&i. and payer_tin_w2_max=payer_tin&i. then stayer=1;
          %end;
          output;
        end;
      else delete;

    run;
%mend;

* run the stayers macro;
%stayers;


* COMPUTE BASIC MEDIAN VARIABLES;
%macro median_by(var, first);

  /* select observations with the specified attribute, and compute counts/medians */
  proc sql;
      create table medians as
      select payer_tin_w2_max, tax_yr, count(*) as &var.s, median(wages) as med_&var._wages from
      (
        select payer_tin_w2_max, tax_yr, wages from final_sample where &var.=1
      )
      group by payer_tin_w2_max, tax_yr;
    quit;

  /* add to the working data set of median wages */
  %if &first.=1 %then %do;

    /* if this is the first run, set the medians to the working data set */
    data working(rename=(pooleds=emps med_pooled_wages=med_wages));
      set medians;
        if pooleds < 3 then med_pooled_wages=.;
    run;

    proc sql;
      drop table medians;
    quit;

  %end;
  %else %do;

    /* logistical thing with proc SQL and not having the same named table */
    proc datasets;
        change working=temp;
      run;

    /* add on the current data to the working */
    proc sql;
        create table working as
        select * from
        temp as a
        full outer join
        (
          select payer_tin_w2_max, tax_yr, &var.s,
                 /* censor the median if there are fewer than 3 employees */
                 case
                   when &var.s >= 3 then med_&var._wages
                                    else .
                 end as med_&var._wages
            from medians
        ) as b
        on     a.payer_tin_w2_max = b.payer_tin_w2_max
           and           a.tax_yr = b.tax_yr;

        drop table temp;
        drop table medians;
      quit;

  %end;

%mend;

/* important that "pooled" goes first */
%median_by(var=     pooled, first=1);
%median_by(var=       male, first=0);
%median_by(var=     female, first=0);
%median_by(var=   inventor, first=0);
%median_by(var=noninventor, first=0);
%median_by(var=     stayer, first=0);


* FIND THE MEDIAN STAYER FOR EACH YEAR IN APPLICATION YEAR, THEN GET THEOR CURRENT YEAR WAGES;
proc sql;

    /* data set with only stayers */
    create table only_stayers as
      select tin, tax_yr, payer_tin_w2_max, applicationyear, wages from final_sample where stayer=1;

    /* data set with the stayers sorted by application year wages */
    create table stayers as
    select *
    from
      only_stayers as a
      inner join
      /* find the application year wages and merge to all observations */
      (
        select tin, payer_tin_w2_max, wages as appyr_wages from final_sample where tax_yr=applicationyear
      ) as b
      on                  a.tin = b.tin
         and a.payer_tin_w2_max = b.payer_tin_w2_max
      inner join
      /* add on the count of stayers in each year (for finding median individual) */
      (
        select payer_tin_w2_max, tax_yr, count(*) as stayers from only_stayers group by payer_tin_w2_max, tax_yr
      ) as c
      on     a.payer_tin_w2_max = c.payer_tin_w2_max
         and           a.tax_yr = c.tax_yr
    order by a.payer_tin_w2_max, a.tax_yr, b.appyr_wages;

  quit;


* find the median app year individual stayers' wages (two if even, one if odd number of stayers);
data median_stayer(keep=payer_tin_w2_max tax_yr appyr_wages);
  set stayers;

    /* determine the rank order by checking against the last observation (if different, reset count) */
    lag_tin = lag1(payer_tin_w2_max);
    lag_yr  = lag1(tax_yr);
    if (lag_tin ne payer_tin_w2_max) or (lag_yr ne tax_yr) then count=1;
    else count = count+1;

    /* regardless of odd or even, select observation above floored half of total stayers */
    if (count = floor(stayers/2) + 1) then output;
    /* if even, also select the person above the floored half of total stayers */
    else if (mod(stayers , 2)=0) and (count = floor(stayers/2)) then output;
    /* otherwise we don't need to worry about the observation anymore */
    else delete;

    retain count;

run;


* find all individuals with the median wages in each year;
proc sql;
    create table medindiv_stayer_wages as
    select a.payer_tin_w2_max, a.tax_yr,
           /* censor the median if there are fewer than 3 employees */
           case
             when max(a.stayers) >= 3 then mean(a.wages)
                                      else .
           end as medindiv_stayer_wages,
           case
             when max(a.stayers) >= 3 then mean(a.appyr_wages)
                                      else .
           end as medindiv_stayer_appyr_wages
    from
      stayers as a
      inner join
      /* determine median individual(s) by merging median wages onto full stayers data set */
      (
        select distinct payer_tin_w2_max, tax_yr, appyr_wages from median_stayer
      ) as b
      on     a.payer_tin_w2_max = b.payer_tin_w2_max
         and           a.tax_yr = b.tax_yr
         and      a.appyr_wages = b.appyr_wages
    group by a.payer_tin_w2_max, a.tax_yr;
  quit;


* NOW THE APPLICATION YEAR COHORT VARIABLES;
* first the contemporaneous median values (not tracking individuals);
proc sql;
    create table cht_simple as
    select payer_tin_w2_max, tax_yr, count(*) as cht_emps,
           /* censor the median if there are fewer than 3 employees */
           case
             when count(*) >= 3 then median(wages)
                                else .
           end as med_cht_wages
    from
      cohorts group by payer_tin_w2_max, tax_yr;
  quit;

* then find the median individuals wages in each year: first find median individual(s) in app year;
proc sql;
    create table appyr_cht_meds as
    select a.payer_tin_w2_max, a.tin, a.wages, b.stayers as cht_emps
    from
      /* select app year cohort */
      (
        select * from cohorts where tax_yr=applicationyear
      ) as a
      inner join
      /* add on the application cohort sizes by the number of stayers in the application year (previously calculated) */
      working as b
      on     a.payer_tin_w2_max = b.payer_tin_w2_max
         and           a.tax_yr = b.tax_yr
    order by payer_tin_w2_max, wages;
  quit;

* find the median individual(s) wages in the application year cohort;
data median_appyr_cht(keep=payer_tin_w2_max wages);
  set appyr_cht_meds;

    /* determine the rank order by checking against the last observation (if different, reset count) */
    lag_tin = lag1(payer_tin_w2_max);
    if lag_tin ne payer_tin_w2_max then count=1;
    else count = count+1;

    /* regardless of odd or even, select observation above floored half of total app year cohort */
    if (count = floor(cht_emps/2) + 1) then output;
    /* if even, also select the floored half of total app year cohort */
    else if (mod(cht_emps , 2)=0) and (count = floor(cht_emps/2)) then output;
    /* otherwise we don't need to worry about the observation anymore */
    else delete;

    retain count;

run;

* track median individual(s) from the app year cohort over time;
proc sql;
    create table medindiv_cht_wages as
    select x.payer_tin_w2_max, x.tax_yr,
           /* censor the median if there are fewer than 3 employees */
           case
             when max(y.cht_emps) >= 3 then mean(x.wages)
                                       else .
           end as medindiv_cht_wages,
           case
             when max(y.cht_emps) >= 3 then mean(x.appyr_wages)
                                       else .
           end as medindiv_cht_appyr_wages
    from
      cohorts as x
      inner join
      /* find all individuals with the median app year wages */
      (
        select a.payer_tin_w2_max, a.tin, a.cht_emps from
        appyr_cht_meds as a
        inner join
        (
          select distinct payer_tin_w2_max, wages from median_appyr_cht
        ) as b
        on     a.payer_tin_w2_max = b.payer_tin_w2_max
           and            a.wages = b.wages
      ) as y
      on     x.payer_tin_w2_max = y.payer_tin_w2_max
         and              x.tin = y.tin
    group by x.payer_tin_w2_max, x.tax_yr;
  quit;


* PRODUCE THE FINAL DATA SET;
proc sql;
    create table output_data as
    select x.payer_tin_w2_max,
           x.tax_yr,
           coalesce(x.emps,0) as emps,
           x.med_wages,
           coalesce(x.males,0) as males,
           x.med_male_wages,
           coalesce(x.females,0) as females,
           x.med_female_wages,
           coalesce(x.inventors,0) as inventors,
           x.med_inventor_wages,
           coalesce(x.noninventors,0) as noninventors,
           x.med_noninventor_wages,
           coalesce(x.stayers,0) as stayers,
           x.med_stayer_wages,
           x.medindiv_stayer_wages,
           x.medindiv_stayer_appyr_wages,
           coalesce(x.cht_emps,0) as cht_emps,
           x.med_cht_wages,
           x.medindiv_cht_wages,
           x.medindiv_cht_appyr_wages
    from
      /* merge all median wage data sets together */
      (
        select * from
        working as a
        full outer join
        medindiv_stayer_wages as b
        on     a.payer_tin_w2_max = b.payer_tin_w2_max
           and           a.tax_yr = b.tax_yr
        full outer join
        cht_simple as c
        on     a.payer_tin_w2_max = c.payer_tin_w2_max
           and           a.tax_yr = c.tax_yr
        full outer join
        medindiv_cht_wages as d
        on     a.payer_tin_w2_max = d.payer_tin_w2_max
           and           a.tax_yr = d.tax_yr
      ) as x
      inner join
      /* then merge to the list of final firms */
      taad as y
      on x.payer_tin_w2_max=y.tin
    order by x.payer_tin_w2_max, x.tax_yr;
  quit;

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