/* plot of all parameters in each segment */ proc sql; create table plot_all as select x.*, y.* from bsr2016.data_subset as x left join bsr2016.all_segtrend_stats as y on x.segment_id = y.segment_id and x.parameter = y.parameter; quit; data plot_all (drop = units2); set plot_all; if parameter in ('Days Since Last Rain' 'Flow Severity' 'Total Hardness' 'Total Organic Carbon' 'Volatile Suspended Solids' 'Orthophosphate-P') then delete; run; proc sort data = plot_all; by segment_id parameter end_date; run; proc sort data = plot_all nodupkey out = info1; by segment_id parameter; run; proc sql; create table info2 as select x.*, y.units2 from info1 as x left join crp_misc.units as y on strip(x.parameter) = strip(y.parameter); quit; data segment_information ; length lintrend2 $20 corrtrend $20 factrend $20 sktrend $25 survtrend $25 units2 $40; set info2; lintrend2 = 'Stable' ; corrtrend= 'Stable' ; factrend='Stable'; sktrend = 'Stable'; if substr(lintrend,1,1) = 'D' then do; lintrend2 = 'Decreasing' ; end; if substr(lintrend,1,1) = 'I' then do; lintrend2 = 'Increasing' ; end; if substr(FAC_trend,1,1) = 'D' then do; factrend = 'Decreasing' ; end; if substr(FAC_trend,1,1) = 'I' then do; factrend = 'Increasing' ; end; if substr(FAC_trend,1,1) = 'N' then do; factrend = 'Stable' ; end; if substr(FAC_trend,1,3) = 'INS' then do; factrend = 'Insufficient data' ; end; if substr(trend_corr,1,1) = 'D' then do; corrtrend = 'Decreasing' ; end; if substr(trend_corr,1,1) = 'I' then do; corrtrend = 'Increasing' ; end; if substr(seasonal_kendall,1,1) = 'd' then do; sktrend = 'Decreasing' ; end; if substr(seasonal_kendall,1,3) = 'ins' then do; sktrend = 'Insufficient data' ; end; if substr(seasonal_kendall,1,3) = 'inc' then do; sktrend = 'Increasing' ; end; if strip(seasonal_kendall) = '' then do; sktrend = 'Not calculated'; end; if strip(fac_trend) = '' then do; factrend = 'Not calculated'; end; if strip(surtrend) = '' then do; survtrend = 'Not Applicable'; end; if substr(surtrend,1,1) = 'S' then do; survtrend = 'Stable'; end; if substr(surtrend,1,1) = 'I' then do; survtrend = 'Increasing'; end; if substr(surtrend,1,1) = 'D' then do; survtrend = 'Decreasing'; end; if parameter = 'pH' then do; std1 = 1*ph2; std2 = 1*ph3; type = '2014 Water Quality Standard' ; standard_text = strip(ph_note2); end; ylabel = strip(parameter)||" - "||units2; run; data segment_information (drop = seg_type); set segment_information; if strip(watershed) = '' then delete; if strip(parameter) = '' then delete; run; proc sql; create table b2 as select x.*, y.seg_type from segment_information as x left join crp_misc.wqs as y on x.segment_id = y.segment_id ; quit; proc sort data = b2 nodupkey; by segment_id parameter; run; data b3; set b2; if length (segment_id) = 5 then do; seg_type2 = 'Unclassified ' ||seg_type; end; if length (segment_id) = 4 then do; seg_type2 = 'Classified '||seg_type; end; run; data bsr2016.all_segment_information; set b3; run; proc sort data = plot_all; by segment_id parameter end_date; run; proc sql; create table dat2b as select distinct segment_id, segment_name, watershed, parameter, Units2, ylabel, standard_text, std1, std2, type as standard_type, samples, corrtrend, lintrend2, seg_type, seg_type2, factrend, sktrend, survtrend, ph_note2 from bsr2016.all_segment_information ; quit; proc sort data = dat2b nodupkey; by segment_id parameter; run; data dat2c; set dat2b; if parameter = 'Enterococci' then do; standard_type = '2014 Water Quality Standard'; end; if std1 > 0 then do; footnote1="If present, dashed red line represents "||strip(standard_type)||": "||strip(standard_text);end; if parameter= 'pH' then do; footnote1 = "If present, dashed red line represents 2014 Water Quality Standard of "||strip(standard_text)||" S.U."; end; if substr(seg_type,1,2) in ('F' 'R') and parameter = 'Enterococci' then do; footnote1= ' '; end; run; data dat2a; set dat2c; obs = _n_; if std1 = . then do; std1 = 9999999999; end; if std2 = . then do; std2 = 999999999; end; if strip(parameter) = '' then delete; run; proc sql noprint; select count(*) into :c from dat2a; quit; /*!!!!!!!!!!!!!!! DELETE CURRENT OUTPUT IN FOLDER BEFORE RUNNING !!!!!B */ %put &c; %macro chart2f; %do i =1 %to 50; /* assign the macro variables */ data _null_; set dat2a; where obs = &i; call symputx("n",watershed); call symputx("s",segment_id); call symputx ("sn",segment_name); call symputx("p",parameter); call symputx("u",ylabel); call symputx("w1",std1); call symputx("w2",std2); call symputx("cor",corrtrend); call symputx("fac",factrend); call symputx("sk",sktrend); call symputx("ln",lintrend2); call symputx("sv",survtrend); call symputx("ns",samples); call symputx("fn",footnote1); call symputx("ty",stream_type); call symputx ("fn", footnote1); call symputx ("st", seg_type2); call symputx ("wq", standard_type); run; %put &rf1 &rf2 &rf3 &n &s &sn &p &u &w1 &w2 &cor &fac &sk &fn &ln &sv &ns &fn &ty &st &wq; ods html image_dpi = 300; ods graphics on / imagefmt = png imagename = "&s._TrSeg_&p._inset1" ; ods html path = "C:\users\hoffman\" gpath = "G:\CE\Databases\Clean_Rivers_Program\SAS_Data_Processing\Project Code and Data\BSR 2016\Charts2\Segment Trends - all" (url="png/") file = "test.htm"; /* ods rtf file = "G:\CE\Databases\Clean_Rivers_Program\SAS_Data_Processing\Project Code and Data\BSR 2016\Charts2\Segment Trends\ &s._TrSeg_&p._&cl. &ty..rtf" ; */ /*start macro do loop */ title1 f='Gotham Narrow Bold' c= 'CX002673' h=12pt " Segment: &s. &sn. "; title2 f='Gotham Narrow Bold' c= 'CX002673' h=11pt " Parameter: &p. Water Body Type: &st."; footnote1 f='Gotham Narrow Bold' c= 'CX002673' h=8pt "Locally-Weighted Least Squares (LOESS) Plot" ; footnote2 f='Gotham Narrow Bold' c= 'CX002673' h=8pt "&fn."; proc sgplot data=plot_all noautolegend ; xaxis label = ' '; yaxis type = log logbase = 10 label = "&u." labelattrs = (color = 'CX002673' family = 'Arial BK BT' ) valueattrs = (color = 'CX002673'); y2axis labelattrs = (color = 'CX002673' family = 'Arial BK BT' ) valueattrs = (color = 'CX002673'); loess x = end_date y = value_adj/ smooth = 0.95 markerattrs=(symbol=circlefilled color=blue size = 3 ) lineattrs = (color= 'CX002673' thickness=2 pattern=solid) legendlabel = "&p." name = "&p."; refline &w1 &w2 / legendlabel = "Standard or Screening Level" name = "wq" lineattrs = (color='CXCC0000' thickness=2 pattern=ShortDash); where segment_id="&s" and parameter="&p"; inset "Number of Samples : &ns." "Correlation Trend : &cor." "Flow-Adjusted Trend : &fac." "Seasonal Kendall/Sen Slope Trend : &sk." "Linear Regression Trend = &ln." "Survival Analysis Trend : &sv."/ position = topleft border textattrs= (family = arial color = 'CX002673' weight = bold size = 4) ; keylegend '&p.' 'wq' / across = 1 position = bottomright valueattrs = (family = 'Gotham Narrow Bold' color= 'CX002673' ); Run; Quit; title; footnote; %end; %mend chart2f; %chart2f; /* all parameters, all stations in CMS */ proc sql; create table plot_all_sta as select x.*, y.* from bsr2016.data as x left join bsr2016.all_stationtrend_stats as y on x.station_id = y.station_id and x.parameter = y.parameter; quit; data plot_all_sta (drop = units2 segment_name); set plot_all_sta; if parameter in ('Days Since Last Rain' 'Flow Severity' 'Total Hardness' 'Total Organic Carbon' 'Volatile Suspended Solids' 'Orthophosphate-P') then delete; run; proc sort data = plot_all_sta; by station_id parameter end_date; run; proc sort data = plot_all_sta nodupkey out = info1; by station_id parameter; run; proc sql; create table info2 as select x.*, y.units2 from info1 as x left join crp_misc.units as y on strip(x.parameter) = strip(y.parameter); quit; proc sql; create table info3 as select x.*, y.seg_type2, y.segment_name from info2 as x left join bsr2016.all_segment_information as y on x.segment_id = y.segment_id; quit; data station_information (drop = ph1 ph2 ph3) ; length lintrend2 $20 corrtrend $20 factrend $20 sktrend $25 survtrend $25 units2 $40; set info3; lintrend2 = 'Stable' ; corrtrend= 'Stable' ; factrend='Stable'; sktrend = 'Stable'; if substr(lintrend,1,1) = 'D' then do; lintrend2 = 'Decreasing' ; end; if substr(lintrend,1,1) = 'I' then do; lintrend2 = 'Increasing' ; end; if substr(FAC_trend,1,1) = 'D' then do; factrend = 'Decreasing' ; end; if substr(FAC_trend,1,1) = 'I' then do; factrend = 'Increasing' ; end; if substr(FAC_trend,1,1) = 'N' then do; factrend = 'Stable' ; end; if substr(FAC_trend,1,3) = 'INS' then do; factrend = 'Insufficient data' ; end; if substr(trend_corr,1,1) = 'D' then do; corrtrend = 'Decreasing' ; end; if substr(trend_corr,1,1) = 'I' then do; corrtrend = 'Increasing' ; end; if substr(seasonal_kendall,1,1) = 'd' then do; sktrend = 'Decreasing' ; end; if substr(seasonal_kendall,1,3) = 'ins' then do; sktrend = 'Insufficient data' ; end; if substr(seasonal_kendall,1,3) = 'inc' then do; sktrend = 'Increasing' ; end; if strip(seasonal_kendall) = '' then do; sktrend = 'Not calculated'; end; if strip(lintrend) = '' then do; lintrend2 = 'Not calculated'; end; if strip(fac_trend) = '' then do; factrend = 'Not calculated'; end; if strip(surtrend) = '' then do; survtrend = 'Not Applicable'; end; if substr(surtrend,1,1) = 'S' then do; survtrend = 'Stable'; end; if substr(surtrend,1,1) = 'I' then do; survtrend = 'Increasing'; end; if substr(surtrend,1,1) = 'D' then do; survtrend = 'Decreasing'; end; ph1 = compress(ph_note2); ph2=substr(ph1,1,3); ph3=substr(ph1,5,3); std1 = standard; if parameter = 'pH' then do; std1 = 1*ph2; std2 = 1*ph3; type = '2014 Water Quality Standard' ; standard_text = strip(ph_note2); end; ylabel = strip(parameter)||" - "||units2; footnote1 = 'Locally-Weighted Least Squares (LOESS) Plot'; if parameter = 'Enterococci' then do; standard_type = '2014 Water Quality Standard'; end; if standard > 0 then do; footnote2="If present, dashed red line represents "||strip(type)||": "||strip(standard_text); end; if parameter= 'pH' then do; footnote2 = "If present, dashed red line represents 2014 Water Quality Standard of "||strip(standard_text)||" S.U."; end; if substr(seg_type,1,2) in ('F' 'R') and parameter = 'Enterococci' then do; footnote2= ' '; end; run; data station_information_all; set station_information ; if strip(watershed) = '' then delete; if strip(parameter) = '' then delete; ylabel = strip(parameter)||" - "||units2; run; proc sort data = plot_all_sta; by station_id parameter end_date; run; proc sql; create table dat3 as select distinct station_id, segment_id, segment_name, au_id, watershed, seg_type, seg_type2, parameter, Units2, ylabel, type, standard_text, std1, std2, samples, corrtrend, lintrend2, factrend, sktrend, survtrend,footnote1 , footnote2 from station_information_all ; quit; data dat3a; set dat3; obs = _n_; if std1 = . then do; std1 = 999999999; end; if std2 = . then do; std2 = 999999999; end; run; proc sql noprint; select count(*) into :c from dat3a; quit; %put &c; %macro chart3; %do i =1 %to &c; /* assign the macro variables */ data _null_; set dat3a; where obs = &i; call symputx("st", station_id); call symputx( "au",au_id); call symputx("sn",segment_name); call symputx("sg", segment_id); call symputx("s",station_id); call symputx("p",parameter); call symputx("u",ylabel); call symputx("w1",std1); call symputx("w2",std2); call symputx("cor",corrtrend); call symputx("fac",factrend); call symputx("sk",sktrend); call symputx("ln",lintrend2); call symputx("sv",survtrend); call symputx("ns",samples); call symputx("fn",footnote1); call symputx ("fn2", footnote2); call symputx("ty",seg_type2); run; %put &st &au &n &sg &s &p &u &w1 &w2 &cor &fac &sk &ln &sv &ns &fn &fn2 &ty ; ods html image_dpi = 300; ods graphics on / imagefmt = png imagename = "&sg._&s._TrSta_&p._inset1" ; ods html path = "C:\users\hoffman\" gpath = "G:\CE\Databases\Clean_Rivers_Program\SAS_Data_Processing\Project Code and Data\BSR 2016\Charts2\Station Trends - all" (url="png/") file = "test.htm"; /* ods rtf file = "G:\CE\Databases\Clean_Rivers_Program\SAS_Data_Processing\Project Code and Data\BSR 2016\Charts2\Segment Trends\ &s._TrSeg_&p._&cl. &ty..rtf" ; */ /*start macro do loop */ title1 f='Gotham Narrow Bold' c= 'CX002673' h=11pt " Segment: &sg. &sn. "; title2 f='Gotham Narrow Bold' c= 'CX002673' h=10pt "Monitoring Station &st. Parameter: &p."; title3 f='Gotham Narrow Bold' c= 'CX002673' h=9pt " Assessment Unit &au. Water Body Type: &ty."; footnote1 justify = center f='Gotham Narrow Bold' c= 'CX002673' h=8pt "&fn" ; footnote2 justify = center f='Gotham Narrow Bold' c= 'CX002673' h=8pt "&fn2" ; proc sgplot data=plot_all_sta noautolegend ; xaxis label = ' '; yaxis type = log logbase = 10 label = "&u" labelattrs = (color = 'CX002673' family = 'Arial BK BT' ) valueattrs = (color = 'CX002673'); loess x = end_date y = value_adj/ smooth = 0.95 markerattrs=(symbol=circlefilled color=blue size = 3 ) lineattrs = (color= 'CX002673' thickness=2 pattern=solid) legendlabel = "&p." name = "&p.";; refline &w1 &w2 / lineattrs = (color='CXCC0000' thickness=2 pattern=ShortDash); where station_id="&st" and parameter="&p"; inset "Number of Results = &ns." "Correlation Trend = &cor." "Flow-Adjusted Trend = &fac." "Seasonal Kendall/Sen Slope Trend = &sk." "Linear Regression Trend = &ln." "Survival Analysis Trend = &sv."/ position = topleft border textattrs= (family = arial color = 'CX002673' weight = bold size = 4) ; Run; Quit; title; footnote; %end; %mend chart3; %chart3;