x cd /pub/data/mosenior/Tools;  %let pgm=index_scores;  filename pgm "&pgm..sas";
*----Create special index measures for the moseniors database using a methodology that involves caculating z-scores which
     are then summed and percentile-ranked by year---;

    title "&pgm : mosenior.master2x";
%let year=2006;
libname mosenior '/pub/data/mosenior';
**libname newdata  "/pub/data/mosenior/newdata&year";
  libname user '/tmp/scratch/user';

/* =======================Begin comment================
 data ma;  set mosenior.mastermavg(keep=sumlev fipco county years diabetes_combined_rate);
   where sumlev='050' and years='2002-2004';
   Year='2003';
   keep fipco county year dia: ;
   run;

 data stdin;
  merge
  mosenior.master1(keep=fipco county sumlev year pctssi pctOver65inWorkForce AvgEarn
   pctjoint_65ov pctmodl65 ftedocspersr  medicaidpercapita cindxp1k engagement_index
   whenrun
   where=(sumlev='050' and year in ('2003','2004','2005')))
  ma(in=inma);  by year fipco;
   if _n_=1 then put '*****************Using master1 data last updated ' whenrun ' ******************';
   drop whenrun;
   run;



 proc standard data=stdin out=zscores     mean=0 std=1;  by year;
   run;

 data torank;
   set zscores;
   by year;
   format pctssi pctOver65inWorkForce
   pctjoint_65ov pctmodl65 ftedocspersr  medicaidpercapita cindxp1k engagement_index 7.4;
   pctssi=-pctssi;
   diabetes_combined_rate=-diabetes_combined_rate;
   cindxp1k=-cindxp1k;
   medicaid_percapita=-medicaidpercapita;
   run;

 proc rank data=torank descending  out=mosenior.master2x(label='Special index indicators');  by year;
   var pctssi pctOver65inWorkForce
   pctjoint_65ov pctmodl65 diabetes_combined_rate ftedocspersr  medicaidpercapita cindxp1k engagement_index;

   ranks pctssi_rank pctOver65inWorkForce_rank
   pctjoint_65ov_rank pctmodl65_rank diabetes_combined_rate_rank ftedocspersr_rank  medicaidpercapita_rank
    cindxp1k_rank  engagement_index_rank;

   run;

 proc print data=&syslast;
   title2 "&syslast";
   by year;
   id fipco county;
   run cancel;

 *----Use collapse macro to create restructured version of master2x with 1 obs per county.---;
 proc sort data=mosenior.master2x out=master2x;
   by fipco year;
   run;

options mprint;
 %collapse(setin=master2x,setout=master2x_collapsed,
   byvars=fipco,idvars=county,
   colvars=pctjoint_65ov          pctssi pctOver65inWorkForce
   pctmodl65 diabetes_combined_rate ftedocspersr  medicaidpercapita cindxp1k engagement_index
    pctjoint_65ov_rank  pctssi_rank pctOver65inWorkForce_rank
    pctmodl65_rank diabetes_combined_rate_rank ftedocspersr_rank  medicaidpercapita_rank
    cindxp1k_rank  engagement_index_rank,
   nslots=3,
   varsufxs=03 04 05,
   debug=0);


 data master2xC;
   set master2x_collapsed;
   keep fipco county
        pctssi03    pctover65inworkforce04     pctjoint_65ov04
        pctmodl6505    diabetes_combined_rate03   ftedocspersr04   cindxp1k05   medicaidpercapita05    engagement_index05
        pctssi_rank03    pctover65inworkforce_rank04     pctjoint_65ov_rank04
        pctmodl65_rank05    diabetes_combined_rate_rank03   ftedocspersr_rank04   cindxp1k_rank05
        medicaidpercapita_rank05    engagement_index_rank05 composite_total;
   composite_total=sum(of
        pctssi03    pctover65inworkforce04     pctjoint_65ov04
        pctmodl6505    diabetes_combined_rate03   ftedocspersr04   cindxp1k05   engagement_index05);
   run;

  proc rank data=master2xC descending out=final_ranks(keep=fipco composite_total_rank);
    var composite_total;
    ranks composite_total_rank;
    run;
  data mosenior.master2xc(label='Master2x data with time dimension across, selected indicators');
    merge master2xc final_ranks;  by fipco;
    run;




  proc print data=mosenior.master2xC;
   title2 'mosenior.master2xC';
   id county;
   var pctjoint_65ov_rank04 -- composite_total_rank;
   run;
     ======================End comment================== */

 *==================================Part II: Generate the master_final_pdf.txt file===========================;
 filename finaltxt '/pub/data/mosenior/master_final_pdf.txt';


 data mosenior.outcome_status_indicators;
  *---data used on pdf report page.  Selected var for slected years.---;
  data ma2;  set mosenior.mastermavg(keep=sumlev fipco county years diabetes_combined_rate);
   *where sumlev='050' and years='2002-2004';
   if years='2002-2004' then Year='2003';
    else if years='1999-2001' then Year='2000';
   keep fipco county year diabetes_combined_rate;
   run;

 data merged;
  merge
  mosenior.master1(keep=fipco county sumlev year pctssi pctOver65inWorkForce
   pctjoint_65ov pctmodl65 ftedocspersr  medicaidpercapita cindxp1k engagement_index
   where=(('2000' <= year  <= '2005')))
  ma(in=inma);  by year fipco;
  run;
 proc sort;  by fipco;  run;
 data ;
   set merged;  by fipco;
   if fipco='29000' then do;  *--save latest values of indicators for the state---;
   select (year);
    when('2003') do;
     diabetes_state=diabetes_combined_rate ;
     pctssi_state=pctssi;
     end;
   when('2004') do;
     pctjoint_state=pctjoint_65ov;
     pctover65inworkforce_state=pctover65inworkforce;
     ftedocspersr_state=ftedocspersr;
     end;
   when('2005') do;
     pctmodl65_state=pctmodl65;
     medicaid_percapita_state=medicaid_percapita;
     cindxp1k_state=cindxp1k;
































 data _null_;
  set mosenior.master2xc;
  retain headerline

  retain headerline
'fipco|County|compositerank|
 pctjoint_65ov.base.yr|pctjoint_65ov.base|pctjoint_65ov.current.yr|pctjoint_65ov.current|pctjoint_65ov.trend|
 pctjoint_65ov.state|pctjoint_65ov.rank|
 pctssi.base.yr|pctssi.base|pctssi.current.yr|pctssi.current|pctssi.trend|pctssi.state|pctssi.rank|
 pctOver65inWorkForce.base.yr|pctOver65inWorkForce.base|pctOver65inWorkForce.current.yr|pctOver65inWorkForce.current|
 pctOver65inWorkForce.trend|pctOver65inWorkForce.state|pctOver65inWorkForce.rank|pctMoDL65.base.yr|pctMoDL65.base|
 pctMoDL65.current.yr|pctMoDL65.current|pctMoDL65.trend|pctMoDL65.state|pctMoDL65.rank|pctDiabetes.base.yr|
 pctDiabetes.base|diabetescombrate.current.yr|diabetescombrate.current|diabetescombrate.trend|diabetescombrate.state|
 diabetescombrate.rank|ftedocsPerSR.base.yr|ftedocsPerSR.base|ftedocsPerSR.current.yr|ftedocsPerSR.current|
 ftedocsPerSR.trend|ftedocsPerSR.state|ftedocsPerSR.rank|MedicaidPerCapita.base.yr|MedicaidPerCapita.base|
 MedicaidPerCapita.current.yr|MedicaidPerCapita.current|MedicaidPerCapita.trend|MedicaidPerCapita.state|
 MedicaidPerCapita.rank|cindxp1k.base.yr|cindxp1k.base|cindxp1k.current.yr|cindxp1k.current|cindxp1k.trend|
 cindxp1k.state|cindxp1k.rank|engagement.base.yr|engagement.base|engagement.current.yr|engagement.current|
 engagement.trend|engagement.state|engagement.rank|totpop.base.yr|totpop.base|totpop.current.yr|totpop.current|
 popchnge|pctchnge|pctchnge65+|over65.base.yr|over65.base|PctOver65.base.yr|PctOver65.base|PctFemalesOver65.base.yr|
 PctFemalesOver65.base|PctMalesOver65.base.yr|PctMalesOver65.base|over65.current.yr|over65.current|
 PctOver65.current.yr|PctOver65.current|PctFemalesOver65.current.yr|PctFemalesOver65.current|
 PctMalesOver65.current.yr|PctMalesOver65.current|PctOver65.est1.yr|PctOver65.est1|PctFemalesOver65.est1.yr|
 PctFemalesOver65.est1|PctMalesOver65.est1.yr|PctMalesOver65.est1|PctOver65.est2.yr|PctOver65.est2|
 PctFemalesOver65.est2.yr|PctFemalesOver65.est2|PctMalesOver65.est2.yr|PctMalesOver65.est2|PctOwnerOv65.yr|
 PctOwnerOv65|pctinfamily65ov.yr|pctinfamily65ov|medvalue_ownrocc.yr|medvalue_ownrocc|PctPoor65ov.yr|PctPoor65ov|
 avghhinc65ov.yr|avghhinc65ov|PctCollGrad.yr|PctCollGrad|cursmoker.yr|cursmoker|inactivity.yr|inactivity|obese.yr|
 obese|hibp.yr|hibp|cholesterol.yr|cholesterol|nomammogram1yr.yr|nomammogram1yr|noscopyin10.yr|noscopyin10';




















  retain _tab '09'x;
 file finaltxt;
 if _n_=1 then do;
   headerline=translate(headerline,'09'x,'|');
   put headerline;
   end;
   put fipco _tab County _tab composite_total_rank _tab @;
   array base(*) pctjoint_65ov04