ods html close; ods html;
options / orientation = landscape;
title "AU in watershed";
proc sql; select count(au_id) from crp_misc.allauid_in_hgcrp where substr(au_id,1,4) = '1102'; quit;
title "AU Assessed" ;
proc sql; select count(distinct au_id) from ir2014.ir where substr(au_id,1,4) = '1102' ; quit;
title;
proc sql; select distinct seg_id, seg_name from crp_misc.Segment_details_tceq6jan2016
where substr(seg_id,1,4) = '1102' order by seg_id; quit;
proc sql; select * from bsr2016.parameter_excursions_segment
where substr(segment_id,1,4) = '1102' order by segment_id; quit;
proc sql;
select * from bsr2016.Parameter_excursions_au where substr(au_id,1,4) = '1102' order by parameter, au_id;
select * from bsr2016.bacteria_au_table where substr (au_id,1,4) = '1102' order by length(au_id), au_id;
select distinct parameter, INTEGRATED_SUPPORT_CODE, count(distinct au_id) from ir2014.ir where substr(au_id,1,4) = '1102'
and INTEGRATED_SUPPORT_CODE in ('NS' ,'CN' ,'CS') group by parameter, INTEGRATED_SUPPORT_CODE;
select segid, au_id, parameter, tceq_cause, MEAN_OF_SAMPLES_ASSESSED as gm label = 'mean/geomean' format = 10.1,
number_of_samples_assessed,
100*NUMBER_OF_EXCEEDANCES/NUMBER_OF_SAMPLES_ASSESSED as pct format = 4.1, assessment_start_date label = 'StartDate' format = mmddyy10.,
ASSESSMENT_END_DATE label = 'EndDate' format = mmddyy10., IMPAIRMENT_CATEGORY label = 'ImpCat',
INTEGRATED_SUPPORT_CODE label = 'SupportCode', use from ir2014.ir
where substr(segid,1,4) = '1102' and INTEGRATED_SUPPORT_CODE in ('NS' ,'CN' ,'CS') order by parameter, au_id, integrated_support_code desc;
select distinct au_id, parameter, tceq_cause, MEAN_OF_SAMPLES_ASSESSED as gm label = 'mean/geomean' format = 10.1,
100*NUMBER_OF_EXCEEDANCES/NUMBER_OF_SAMPLES_ASSESSED as pct format = 4.1, assessment_start_date label = 'StartDate' format = mmddyy10.,
ASSESSMENT_END_DATE label = 'EndDate' format = mmddyy10., IMPAIRMENT_CATEGORY label = 'ImpCat', INTEGRATED_SUPPORT_CODE label = 'SupportCode', use from ir2014.ir
where substr(segid,1,4) = '1102' and substr(parameter,1,4) = 'Diss' and INTEGRATED_SUPPORT_CODE in ('NS' ,'CN' ,'CS') order by integrated_support_code desc, au_id;
quit;
proc sql; create table nut_au as select distinct au_id from ir2014.ir where substr (au_id,1,4) = '1102'
and parameter in ('Total Phosphorus' , 'NItrate', 'Ammonia' 'Chlorophyll-a') and INTEGRATED_SUPPORT_CODE in ('NS' ,'CN' ,'CS') ;
quit;
proc sql; create table nut1 as select x.*, y.* from nut_au as x left join bsr2016.parameter_excursions_au as y
on x.au_id = y.au_id; quit;
data nut1; set nut1; where substr(parameter,1,3) in ('Nit' 'Tot' 'Amm'); run;
data nut1; set nut1; if parameter = 'Total Dissolved Solids' then delete; run;
data Ir2014_nut; set bsr2016.Ir2014_pct_exceed;
where substr(parameter,1,3) in ('Nit' 'Tot' 'Amm') and substr(au_id,1,4) = '1102' and INTEGRATED_SUPPORT_CODE in ('NS' ,'CN' ,'CS') ; run;
proc sql; select x.au_id, x.parameter,x.pct_exceed format = 5.1, y.tp1pct, y.tp2pct from ir2014_nut as x
left join nut1 as y
on x.au_id = y.au_id and substr(x.parameter,1,3) = substr(y.parameter,1,3) where pct_exceed > 0 order by length (x.au_id),x.au_id, x.parameter
; quit;
proc sql;
select * from bsr2016.Parameter_excursions_au
where au_id = '1002_05' order by au_id;
select segid, au_id, parameter, tceq_cause, MEAN_OF_SAMPLES_ASSESSED as gm label = 'mean/geomean' format = 10.1,
100*NUMBER_OF_EXCEEDANCES/NUMBER_OF_SAMPLES_ASSESSED as pct format = 4.1,assessment_start_date label = 'StartDate' format = mmddyy10.,
ASSESSMENT_END_DATE label = 'EndDate' format = mmddyy10., IMPAIRMENT_CATEGORY label = 'ImpCat', INTEGRATED_SUPPORT_CODE label = 'SupportCode', use from ir2014.ir
where au_id = '1002_05' order by integrated_support_code desc; quit;
proc sql; select end_date, value, station_id from bsr2016.data
where station_id = '11204' and parameter = 'pH' and end_date GE '01jun2008'd order by end_date; quit;
proc sql; select distinct au_id, tceq_cause from ir2014.ir where substr(segid,1,4) = '1002' and substr(tceq_cause,1,2) in ( 'am' 'ch', 'ni', 'to') order by au_id; quit;
proc contents data = bsr2016.parameter_excursions_au; run;
proc sql;
select * from bsr2016.Parameter_excursions_au
where segment_id = '1002' order by au_id;