* convert CMU livetock Excel-based CSV to EIS format;
* layers updated late 11may16;
%let agdir=C:\Users\RMASON\Documents\FY2016\NEI team\NEI2014\ag sectors\livestock\;

%MACRO LIVESTOCK(animal,sccm);
data &animal.(keep=fips animal clim_div ctyname scc emis_tpy emis_kgpy emis_kg emis_mton);
  length animal $8 fips $5 scc $10 ctyname $50 ;
  array emis{365} e1-e365;
  infile "&agdir.2014_&animal.-NH3_emissions.csv"
   firstobs=2 dlm=',' dsd lrecl=10000 missover pad;
  input index $ st $ stfips $ clim_div $ ctyfips $ fips $ ctyname $ emis{*} emis_kg emis_mton;
  if fips ne '';
 SCC="&sccm";
 emis_kgpy=sum(of emis[*]);
 emis_tpy=emis_kgpy / 907.185;
 animal="&animal";
run;
%MEND LIVESTOCK;
%LIVESTOCK(swine,2805025000);
%LIVESTOCK(beef,2805002000);
%LIVESTOCK(dairy,2805018000);
*retired SCC %LIVESTOCK(layers,2805030003);
*retired SCC %LIVESTOCK(broilers,2805030004);
%LIVESTOCK(layers,2805007100); *dry, wet is 8100 -confirmed with CMU that this is the most appropriate SCC;
%LIVESTOCK(broilers,2805009100);

*********** ERRORS in CMU file:
* FIPS=08043, county name=HUERFANO should have FIPS=08055 -already there, delete FIPS=08043 for HUERFANO
* FIPS=17195 is a simple duplicate, remove? No, 2 different climate divisions, so SUM?
* FIPS=21071, 2 different climate divisions, so SUM?
* FIPS=21095, ditto
* FIPS=22091, ditto, though county spelling slight difference
* FIPS=26025, 
* FIPS=35002 should be 35025 (missing)
* 11may16, 2:23pm from CMU: It seems that the most appropriate selection for SCC code would be the 2805007100 (dry management systems);
* wet management is less common and typically refers to farms where manure is scraped into a holding tank and
  solids and the manure has <15% dry matter content.
******************************;
*concat and quick QA;
data layers_rev(keep=fips animal clim_div ctyname scc emis_tpy emis_kgpy emis_kg emis_mton);
  length animal $8 fips $5 scc $10 ctyname $50 ;
  array emis{365} e1-e365;
  infile "&agdir.2014_layers-NH3_emissions_rev11may16.csv"
   firstobs=2 dlm=',' dsd lrecl=10000 missover pad;
  input index $ st $ stfips $ clim_div $ ctyfips $ fips $ ctyname $ emis{*} emis_kg emis_mton;
  if fips ne '';
 SCC='2805007100';
 emis_kgpy=sum(of emis[*]);
 emis_tpy=emis_kgpy / 907.185;
 animal='layers';
run;
* quick QA;
proc sort data=layers(rename=(emis_tpy=emis_tpy_old)); by fips clim_div ctyname; run;
proc sort data=layers_rev; by fips clim_div ctyname; run;
data qa_layers;
 length st $2;
 merge layers(in=a keep=fips ctyname clim_div emis_tpy_old) layers_rev(in=b);
 by fips clim_div ctyname;
 new_minus_old = emis_tpy - emis_tpy_old;
 st=substr(fips,1,2);
run;
proc sort data=qa_layers; by st; run;
proc summary data=qa_layers sum;
 by st;
 var emis_tpy emis_tpy_old new_minus_old;
 output out=qa_layers_st(drop=_:) sum=;
run;
proc summary data=qa_layers sum;
  var emis_tpy emis_tpy_old new_minus_old;
 output out=qa_layers_us(drop=_:) sum=;
run;
* small diffs: 62 tons less in new layers dataset;
data ls;
 length fips $5 scc $10 poll $16 uom $3 emission_methodology_code 8. Emission_Comment $100;
 retain poll 'NH3' uom 'TON' emission_methodology_code 8 ;
 set swine beef dairy layers_rev broilers;
 Emission_Comment = '2014 CMU semi-empirical process-based, provided 05may2016)';
run;
proc sort data=ls; * nodupkey; by fips scc poll; run;
** find duplicates;
data dups;
 set ls;
 by fips scc poll;
 if sum(first.poll,last.poll) ne 2;
run;
proc export data=dups
 outfile="&agdir.2014_CMU_duplicates_new.csv"
  replace;
run;
proc summary data=ls sum;
 var emis_tpy emis_kgpy emis_kg emis_mton;
 output out=ls_total(drop=_:) sum=;
run;
* make suggested duplicate fixes and export for EIS QA;
data ls_fin;
 set ls;
 if FIPS='08043' and ctyname='HUERFANO' then delete;
 if fips='35002' then do;
  fips='35025'; ctyname='LEA';
 end; * fix via ALyssa 11may16, 2:54pm;
run;
proc sort data=ls_fin;* nodupkey; by fips scc poll; run;
proc summary data=ls_fin sum;
 by fips scc poll;
  id uom emission_methodology_code Emission_Comment animal clim_div ctyname;
 var emis_tpy;
 output out=ls_fin2(drop=_:) sum=;
run;
***********************************************************************;
*** THIS IS THE EIS-ready INVENTORY: **********************************;
proc export data=ls_fin2
 outfile="&agdir.2014_CMU_Livestock_with_final_layers_12may2016.csv"
  replace;
run;
***********************************************************************;
***********************************************************************;
* Generate some simple 2014 summaries;
***********************************************************************;
* create quick state/SCC summary for 2014;
data qa_tot;
 length st $2 state $30;
 set ls_fin2;
 st=substr(fips,1,2);
 state = fipnamel(st);
run;
proc sort data=qa_tot; by st state scc; run;
proc summary data=qa_tot sum;
 by st state scc;
  id animal;
  var emis_tpy;
  output out=qa_st(drop=_:) sum=;
run;
proc summary data=qa_tot sum;
 by st state;
  var emis_tpy;
  output out=qa_st_tot(drop=_:) sum=;
run;
* US by animal type;
proc sort data=qa_tot out=qa_animal; by scc; run;
proc summary data=qa_animal sum;
 by scc; id animal;
  var emis_tpy;
  output out=qa_animal_tot(drop=_:) sum=;
run;
* export: state/SCC, state and US/SCC;
proc export data=qa_st
 outfile="&agdir.2014_CMU_Livestock_St_SCC_12may2016.csv"
  replace;
run;
proc export data=qa_st_tot
 outfile="&agdir.2014_CMU_Livestock_St_total_12may2016.csv"
  replace;
run;
proc export data=qa_animal_tot
 outfile="&agdir.2014_CMU_Livestock_Contiguous_US_SCC_12may2016.csv"
  replace;
run;
