/* NEED TO INPUT LINE TO CONNECT TO DATABASE */

/* set directories */
libname home 'NEED TO INPUT YOUR VALUE HERE:::SHOULD BE WHERE SORTED QUARTILE_VARIABLES_SORTED.sas7bdat IS LOCATED';
%let csvdir= %str(NEED TO INPUT YOUR VALUE HERE);
options obs=max;

%let fyr = 1996;

/* import full data and compute all required variables */
%macro add_vars;
  data working_data(drop=rc);
    set home.quartile_variables_sorted(keep=tin tax_yr payer_tin_w2_max ssa_yob gnd_ind inventor wages1996 wages1997 wages1998 wages1999 wages2000 wages2001 wages2002 wages2003 wages2004 wages2005 wages2006 wages2007 wages2008 wages2009 wages2010 wages2011 wages2012 wages2013 wages2014) end=eof;

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

    logwages = .;
    if wages ne . and wages > 0 then logwages = log(wages);

    /* define a hash table with the first observed non-zero wage year for each firm */
    length payer_tin_w2_max 8 founding_year 8;

    if _N_ = 1 then do;
      declare Hash Founding ();
        rc = founding.DefineKey ('payer_tin_w2_max');
        rc = founding.DefineData ('payer_tin_w2_max','founding_year');
        rc = founding.DefineDone ();
        call missing(payer_tin_w2_max, founding_year);
    end;

    if payer_tin_w2_max ne 0 and payer_tin_w2_max ne . and wages ne 0 and wages ne . then
    do;

      rc = founding.find();

      /* if the value exists */
      if rc = 0
       then do;
         if founding_year>tax_yr
           then do;
             founding_year = tax_yr;
             rc = founding.replace();
           end;
       end;

      /* if the value does not exist */
      else
        do;
          founding_year = tax_yr;
          rc = founding.ref();
        end;

      end;
    /* end hash table definition */

    /* quadratic age */
    age = tax_yr-ssa_yob;

    /* replace inventor with 0 if missing */
    if inventor=. then inventor=0;

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

    /* previous year's firm */
    lagtin = lag1(payer_tin_w2_max);

    /* previous job's wages */
    change_jobs = (tax_yr>&fyr.)*(payer_tin_w2_max ne lagtin);
    prev_wages = holding_wages;
    holding_wages = lag1(wages);

    if change_jobs=1 and ((lagtin ne . and lagtin ne 0) or prev_val_fj = 1) then prev_wages=holding_wages;
    else if prev_wages=. or  tax_yr=&fyr. then prev_wages=0;
    holding_wages = prev_wages;
    retain holding_wages;

    /* first job */
    if tax_yr = &fyr. then first_job = 1;
    else if change_jobs = 1 and lagtin ne 0 and lagtin ne . then first_job = 0;
    else first_job = prev_val_fj;
    prev_val_fj = first_job;
    retain prev_val_fj;

    /* year hired by current firm */
    if change_jobs=1 or tax_yr = &fyr. then yr_hired = tax_yr;
    else yr_hired = last_val_yr_hired;

    last_val_yr_hired = yr_hired;
    retain last_val_yr_hired;

    /* make variables for regression */
    age_sq = age*age;
    age_cu = age_sq*age;
    age_qu = age_cu*age;
    prev_wages_sq = prev_wages*prev_wages;

    male_age = male*age;
    male_age_sq = male*age_sq;
    male_age_cu = male*age_cu;
    male_age_qu = male*age_qu;

    female_age = female*age;
    female_age_sq = female*age_sq;
    female_age_cu = female*age_cu;
    female_age_qu = female*age_qu;

    inventor_age = inventor*age;
    inventor_age_sq = inventor*age_sq;
    inventor_age_cu = inventor*age_cu;
    inventor_age_qu = inventor*age_qu;

    male_inventor = male*inventor;
    female_inventor = female*inventor;

    /* we do not want values that are zero */
    if payer_tin_w2_max=0 or payer_tin_w2_max=. or wages=0 or wages=. then delete;

    drop ssa_yob gnd_ind change_jobs holding_wages last_val_yr_hired prev_val_fj lagtin;

    /*output the hash table */
    if eof then rc = founding.output(dataset:'founding_year');

  run;
%mend;
%add_vars;

/* import full data and compute all required variables */
%macro predict_vals(var_list, newvar);

  data _null_;
    set ests(keep=Parameter Estimate) end=eof;

      /* declare a hash table */
      if _N_ = 1 then
      do;
        declare Hash estimates ();
          rc = estimates.DefineKey ('tax_yr');
          rc = estimates.DefineData ('tax_yr','Estimate');
          rc = estimates.DefineDone ();
      end;

      /* fill it with values */
      if indexw(Parameter,'tax_yr') then
      do;
        Parameter = translate(Parameter,'','tax_yr');
        Parameter = trim(Parameter);
        tax_yr = 1*Parameter;
        rc = estimates.ref();
      end;

      /*output the hash table */
      if eof then rc = estimates.output(dataset:'estimates');

  run;

  data working_data(drop=rc Estimate);
    set working_data;

    length tax_yr 4 Estimate 8;

    if _N_ = 1 then do;
      declare Hash estimates(dataset:'estimates');
      rc = estimates.DefineKey('tax_yr');
      rc = estimates.DefineData('Estimate');
      rc = estimates.DefineDone();
    end;

    rc = estimates.find();
    &newvar. = &newvar. - Estimate;

  run;

%mend;

/* run the quality index regressions: first baseline then expanded */

/* wrapper for quality regressions */
%macro run_reg(depvar, indepvars, outvar);

  ods trace on;
  proc glm data=working_data;
    class tax_yr;
    model &depvar. = &indepvars. tax_yr / solution;
    output out=working_data predicted=&outvar.;
    ods output ParameterEstimates=ests;
  run;
  ods trace off;
  %predict_vals(var_list=&indepvars., newvar=&outvar.);

%mend;

/* baseline level */
%run_reg(depvar=wages, indepvars=age age_sq age_cu age_qu male female inventor male_age male_age_sq male_age_cu male_age_qu female_age female_age_sq female_age_cu female_age_qu inventor_age inventor_age_sq inventor_age_cu inventor_age_qu male_inventor female_inventor, outvar=baseline_quality)

/* baseline logged */
%run_reg(depvar=logwages, indepvars=age age_sq age_cu age_qu male female inventor male_age male_age_sq male_age_cu male_age_qu female_age female_age_sq female_age_cu female_age_qu inventor_age inventor_age_sq inventor_age_cu inventor_age_qu male_inventor female_inventor, outvar=log_baseline_quality)

/* expanded level */
%run_reg(depvar=wages, indepvars=age age_sq age_cu age_qu male female inventor male_age male_age_sq male_age_cu male_age_qu female_age female_age_sq female_age_cu female_age_qu inventor_age inventor_age_sq inventor_age_cu inventor_age_qu male_inventor female_inventor prev_wages prev_wages_sq first_job, outvar=expanded_quality)

/* expanded logged */
%run_reg(depvar=logwages, indepvars=age age_sq age_cu age_qu male female inventor male_age male_age_sq male_age_cu male_age_qu female_age female_age_sq female_age_cu female_age_qu inventor_age inventor_age_sq inventor_age_cu inventor_age_qu male_inventor female_inventor prev_wages prev_wages_sq first_job, outvar=log_expanded_quality)

/* save the quality index data */
proc sql;
  create table quality as
    select payer_tin_w2_max, tax_yr,
      mean(baseline_quality) as mean_baseline_quality,
      mean(expanded_quality) as mean_expanded_quality,
      mean(log_baseline_quality) as mean_log_baseline_quality,
      mean(log_expanded_quality) as mean_log_expanded_quality
    from working_data
    group by payer_tin_w2_max, tax_yr;
quit;

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


/* assign order values based on the order hash table */
data working_data(drop=rc);
  set working_data(keep=payer_tin_w2_max tax_yr wages logwages yr_hired);
  length payer_tin_w2_max 8 founding_year 8;

  if _N_ = 1 then do;
    declare Hash founding(dataset:'founding_year');
    rc = founding.DefineKey('payer_tin_w2_max');
    rc = founding.DefineData('founding_year');
    rc = founding.DefineDone();
    call missing(payer_tin_w2_max, founding_year);
  end;

  rc = founding.find();
  order = 1+yr_hired-founding_year;
  order_sq = order*order;

  if order=1 then order_cat = 1;
  else if order=2 then order_cat = 2;
  else if order=3 or order=4 then order_cat=3;
  else order_cat=4;

  output;

run;

/* run the order regressions */
proc reg data=working_data(keep=wages order order_sq);
  model wages = order order_sq;
run;

proc reg data=working_data(keep=logwages order order_sq);
  model logwages = order order_sq;
run;

proc glm data=working_data(keep=wages order order_sq payer_tin_w2_max);
  class payer_tin_w2_max;
  model wages = order order_sq payer_tin_w2_max / solution;
run;

proc glm data=working_data(keep=logwages order order_sq payer_tin_w2_max);
  class payer_tin_w2_max;
  model logwages = order order_sq payer_tin_w2_max / solution;
run;

/* collapse to average by firm-year-order category */
proc sql;
  create table order_earn as
    select payer_tin_w2_max, tax_yr, order_cat, sum(wages <> 0 and wages <> .) as num_workers_by_order, mean(wages) as mean_wages_by_order from working_data
    where payer_tin_w2_max <> 0 and payer_tin_w2_max <> .
    group by payer_tin_w2_max, tax_yr, order_cat;
quit;

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