/* Program name: Populate_TNTP_revised2007Jan08 Written by: Anne Hoos Date: 6/20/2005 Modified 10/10/2005, 12/6/2005, 1/8/2007 NOTE: DO NOT RUN THIS PROGRAM ON STORET DATA UNTIL AFTER RUNNING THE PROGRAM THAT CONVERTS REMARK-CODE INFORMATION IN STORET TO FLUXMASTER COMPATIBLE FORMAT!! Calculate value for a total nitrogen (TN) parameter code (P60000). Assign as equal to P00600, or if P00600 is missing: 1. combine total kjeldahl nitrogen (TKN) results and nitrate (NO2+NO3) results 2. combine ammonia nitrogen (AM) results, organic nitrogen (ON) results, and nitrate (NO2+NO3) results, 3. combined dissolved kjeldahl nitrogen (DKN) results, suspended kjeldahl nitrogen (SKN) results, and nitrate (NO2+NO3) results, Calculate value for a total phosphorus (TP) parameter code (P66500). Assign as equal to P00665, or if P00665 is missing: combine dissolved and suspended phosphorus (if available, although this is rarely the case) Rules for combining include how to handle case if one or more of the constituents have censored values, and how to populate remark code for the calculated parameter. TN = TKN + NO3+NO3, and if either TKN or NO2+NO3 are censored then TN is qualified as censored (remark code for TN is set to <). But make an exception to this for the case NO2+NO3 is censored but TKN is not censored and the ratio of the detection limit for NO+NO3 to the result for TKN is less than 0.2. In this special case, TN = TKN + 0.5 * (MDL for NO2+NO3), and remark for TN is set to indicate result is not censored (i.e. set back to missing) When calculating TKN from DKN and SKN, or from AM and ON, used the more 'conservative' approach about handling censored value: TN = DKN + SKN (or TN = AM + ON), and if either of the two components are censored, set combined result to censored (this approach can be considered 'more conservative' in the sense that it always leads to a true statement about the combined result - i.e. how it compares to its calculated censoring limit, but is actually less conservative in that it may lead to unwarranted loss of information) The if/then commands assume that the STORET remark codes are 'cleaned up' to conform to NWIS formats (i.e. have run the SAS program Step _Convert_remarkcoding ) */ /* ------------------------------------------------------------------------------------------ */ /* Modify the following header information each time program is run */ /* Specify the directory where the joined water-quality data set will be created */ libname comb 'K:\regnawqa\MRB-2\Nut_TrendsSparrow\instreamdata\qwdata\STORET_combined\Stationlists_Datafiles\WQData' ; /* Specify the name of the input and output files */ %let input = joinedstoret_fixidrem_sagt ; %let output = joinedstoret_idrem60000_sagt ; /* ------------------------------------------------------------------------------------------ */ /* Open the file */ data temp1; SET comb.&input ; run ; /* Set the 'supercodes' for total nitrogen, nitrate, and kjeldahl from the 'best fit' parameter codes */ data temp2; set temp1; P60000 = P00600 ; R60000 = R00600 ; D60000 = D00600 ; P62500 = P00625 ; R62500 = R00625 ; D62500 = D00625 ; P63000 = P00630 ; R63000 = R00630 ; D63000 = D00630 ; P66500 = P00665 ; R66500 = R00665 ; D66500 = D00665 ; run; /* Populate supercodes in cases where the 'best fit' parameter code were not populated */ data temp3 ; set temp2 ; /* Populate total kjeldahl from P00635 if P00625 was not populated */ /* Error in earlier version, corrected now to set = to P00635, since P63500 has not been populated) */ If P62500 = '.' then do ; P62500 = P00635 ; /* P63500 ; */ R62500 = R00635 ; /* R63500 ; */ D62500 = D00635 ; /* D63500 ; */ end ; /* Populate total kjeldahl from organic plus ammonia if 00625 and 00635 not populated */; /* Added this code on January 8, 2007, when I realized this was required for TVA data */ if P62500 = . and P00605 NE . and P00610 NE . then do ; P62500 = P00605 + P00610 ; if R00605 = '<' or R00610 = '<' then /* R00625 */ R62500 = '<' ; end ; /* Populate total kjeldahl from dissolved plus suspended */; If P62500 = . And P00623 NE . And P00624 NE . Then do ; P62500 = P00623 + P00624 ; if R00623 = '<' or R00624 = '<' then /* R00625 */ R62500 = '<' ; /* code before 12/6/2005 had typo */ end ; If P63000 = '.' Then do ; P63000 = P00631 ; R63000 = R00631 ; D63000 = D00631 ; end; If P63000 = '.' Then do ; P63000 = P00620 ; R63000 = R00620 ; D63000 = D00620 ; end; If P63000 = '.' Then do ; P63000 = P00618 ; R63000 = R00618 ; D63000 = D00618 ; end ; /* Populate total nitrogen from nitrate and kjeldahl 'supercodes' if 00600 not available */ /* The code for populating R60000 in this case starts with a more conservative approach that if either NO2+NO3 or TKN are censored (i.e. R63000 or R62500 are <), TN will also be censored. (However this will be modified below for a special case) */ If P60000 = . And P63000 NE . And P62500 NE . Then do ; P60000 = P63000 + P62500 ; if R63000 = '<' or R62500 = '<' then R60000 = '<' ; /* Following three statements avoid assigning 'less than' to the case where nitrate value is less than but kjeldahl is a measured value and kjeldahl is much larger than (factor of 5) nitrate (which leads to unreasonably large detection limits and loss of useful information. Also in this case, (i.e. when kjeldahl is measured, nitrate is a less than, and kjeldahl value is much much larger than nitrate detection) should set R60000 to indicate not censored (i.e. set back to missing) */ Ratio = P63000 / P62500 ; if R63000 = '<' and R62500 NE '<' and Ratio < 0.2 then R60000 = ''; if R63000 = '<' and R62500 NE '<' and Ratio < 0.2 then P60000 = P62500 + 0.5 * P63000 ; end; /* Need to set a value for D60000 for any cases where it has not yet been populated, because need to avoid Fluxmaster applying to this summed parameter its algorithm for synthesizing a D value to associate with every P value (even those not remarked as less than). (Fluxmaster uses median value from all censored data to do this synthesis, and for other parameter codes this is fine - but in this case may lead to unreasonably synthesized value for D-field ) Decided to use the 50 percentile of the distribution of D00625 in the NWIS data set - 0.1 mg/L (Used the distribution for D00625, not D00600, because D00600 has an extremely wide range (since it is also a synthesized number), and kjeldahl makes up the larger part of total nitrogen so it's actual detection limit is not an unreasonable substitute for an actual detection limit for measuring total nitrogen) */ if D60000 = . and P60000 NE . then D60000 = 0.1 ; /* Based on 50 percentile of the distribution of D00625 in the NWIS data set. */ /* Populate total phosphorus from dissolved plus suspended if 00665 not available */ /* Although it turns out there are no cases in the combined STORET dataset for SAGT where P00665 is missing but both P00666 and P00667 are present, and thus this next code has no effect, it's included here anyway for completeness */ If P66500 = . And P00666 NE . And P00667 NE . Then do ; P66500 = P00666 + P00667 ; if R00666 = '<' or R00667 = '<' then R66500 = '<' ; end; run; data comb.&output ; set temp3 ; run ;