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;