/* This routine resolves the differences among data sources in the format or convention for recording results, by revising the data records retrieved from Legacy and Modernized STORET to match standard (NWIS) convention. (The load estimation program, Fluxmaster, is programmed to work with data coded using the NWIS convention.) This routine also corrects cases of obviously erroneous concentration results, such as extremely large values. NOTE - MUST RUN THIS PROGRAM BEFORE RUNNING THE PROGRAM TO CALCULATE NUTRIENT SUPERCODES! These changes are applied only to the constituents that will be used in calculating supercodes (otherwise programming is too cumbersome!) 00600, 00618 00620 00623 00624 00625 00630 00631 00635 00600 00666 00667 AND AS OF JANUARY 2007: 00610 and 00605 Summary of changes for legacy STORET data records: Replace the nonsense numbers (positive and negative) with missing values Replace the zero and negative values that indicate below detection with appropriate detection limit values, and set Remark Code to '<'. Replace all remark codes that mean '<' (K and U) with '<'. Replace remark codes that mean '>' (L) to '>' Summary of changes for modernized STORET data records: For less than result for which detection-limit was not available in the retrieved data in order to populate the P field during reformatting: set P field equal to a reasonable estimate of detection limit (75 percentile of all detection limits reported in the SAGT project data set from STORET, which can be obtained from distribution of values in the D field. ). Replace all remark codes that mean '<' (U) with '<'. */ /* Specify the directory path and input/output file names */ %let home = K:\regnawqa\MRB-2\Nut_TrendsSparrow\instreamdata\qwdata\STORET_combined\Stationlists_Datafiles\WQData ; %let SAS_inputfile = joinedstoret_fixidchng_wq_sagt ; %let SAS_outputfile = joinedstoret_fixidrem_sagt ; libname dir "&home" ; data temp1 ; set dir.&SAS_inputfile ; /* Section 1: Make changes that apply only to Legacy data */ if source = 'Legacy' then do ; /* Change any occurences of E in Legacy data (E in Legacy mean 'extra sample taken in compositing process) to ' '. Need to change that first, since in subsequent steps will be coding in remarks for some cases with the value E to mean 'estimated value' (NWIS format) */ if R00600 = 'E' then R00600 = ''; /* Change occurence of L in Legacy data to '>' */ If R00600 = 'L' then R00600 = '>' ; /* May also want to add code to handle cases remarked as V, which indicates that associated blank had a detect. For now leave as is */ /* Repeat above sequence for all other codes, for the legacy records only*/ if R00605 = 'E' then R00605 = ''; If R00605 = 'L' then R00605 = '>'; if R00610 = 'E' then R00610 = ''; If R00610 = 'L' then R00610 = '>'; if R00618 = 'E' then R00618 = ''; If R00618 = 'L' then R00618 = '>'; if R00620 = 'E' then R00620 = ''; If R00620 = 'L' then R00620 = '>'; if R00623 = 'E' then R00623 = ''; If R00623 = 'L' then R00623 = '>'; if R00624 = 'E' then R00624 = ''; If R00624 = 'L' then R00624 = '>'; if R00625 = 'E' then R00625 = ''; If R00625 = 'L' then R00625 = '>'; if R00630 = 'E' then R00630 = ''; If R00630 = 'L' then R00630 = '>'; if R00631 = 'E' then R00631 = ''; If R00631 = 'L' then R00631 = '>'; if R00635 = 'E' then R00635 = ''; If R00635 = 'L' then R00635 = '>'; if R00665 = 'E' then R00665 = ''; If R00665 = 'L' then R00665 = '>'; if R00666 = 'E' then R00666 = ''; If R00666 = 'L' then R00666 = '>'; if R00667 = 'E' then R00667 = ''; If R00667 = 'L' then R00667 = '>'; if R80154 = 'E' then R80154 = ''; If R80154 = 'L' then R80154 = '>'; end ; /* Section 2: Make changes that apply only to Modernized data */ if source = 'Modern' then do ; /* Search for occurrence of less than result (R contains < or U) for which no value is stored in the P field and populate with a reasonable estimate of detection limit - such cases occur when no information was given about the detection limit, so the P field could not be populated during SAS reformatting*/ if R00600 = '<' or R00600 = 'U' then do ; if P00600 = . then P00600 = 0.1 ; /* Know that D00600 is blank in this case, so no need to test that first as a source of detection-limit information */ end ; /* Repeat above sequence for all other codes, for the modernized records only*/ if R00605 = '<' or R00605 = 'U' then do ; If P00605 = . then P00605 = 0.05 ; end; if R00610 = '<' or R00610 = 'U' then do ; If P00610 = . then P00610 = 0.05 ; end; if R00618 = '<' or R00618 = 'U' then do ; If P00618 = . then P00618 = 0.05 ; end; if R00620 = '<' or R00620 = 'U' then do ; If P00620 = . then P00620 = 0.04 ; end; if R00623 = '<' or R00623 = 'U' then do ; If P00623 = . then P00623 = 0.25 ; end; if R00624 = '<' or R00624 = 'U' then do ; If P00624 = . then P00624 = 0.1 ; end; if R00625 = '<' or R00625 = 'U' then do ; If P00625 = . then P00625 = 0.1 ; end; if R00630 = '<' or R00630 = 'U' then do ; If P00630 = . then P00630 = 0.02 ; end; if R00631 = '<' or R00631 = 'U' then do ; If P00631 = . then P00631 = 0.01 ; end; if R00635 = '<' or R00635 = 'U' then do ; If P00635 = . then P00635 = 0.1 ; end; if R00665 = '<' or R00665 = 'U' then do ; If P00665 = . then P00665 = 0.05 ; end; if R00666 = '<' or R00666 = 'U' then do ; If P00666 = . then P00666 = 0.01 ; end; if R00667 = '<' or R00667 = 'U' then do ; If P00667 = . then P00667 = 0.05 ; end; if R80154 = '<' or R80154 = 'U' then do ; If P80154 = . then P80154 = 2.4 ; end; end; /* Section 3: Make changes that apply to both Modernized and Legacy data records */ /* Get rid of the nonsense positive numbers - replace with missing values and remark code */ If P00600 > 900 then do ; P00600 = . ; R00600 = '' ; end; /* Get rid of the nonsense negative numbers - replace with missing values and remark codes */ If P00600 < -900 THEN DO ; P00600 = . ; R00600 = '' ; end; /* Replace zero values with detection limit and code as less than, unless remark code is 'J' 'O' 'Q' or 'Y', (which is a possibility for legacy data records only) in which case there is no meaningful way to interpret a zero result, and thus not reasonable to replace with a less than */ If P00600 = 0.0 then do ; If R00600 = 'J' or R00600 = 'O' or R00600 = 'Q' or R00600 = 'Y' then P00600 = .; else R00600 = '<' ; end; If P00600 = 0.0 then do ; If D00600 > 0 then P00600 = D00600; else P00600 = 0.05 ; end ; /* Replace negative values (other than nonsense which were handled in a previous step) with the inverse value and set remark code either to < or, if remark code had been T or W or I, set equal to E. */ If P00600 < 0.0 and P00600 NE . then do ; /* The reason for adding the NE . to this criterio: want to make sure to exclude missing values, and specifying < 0.0 doesn't seem to exclude missing values (a quirk of SAS? */ P00600 = P00600 * -1.0 ; If R00600 = 'T' or R00600 = 'W' or R00600 = 'I' then R00600 = 'E'; else R00600 = '<' ; end ; /* Repeat above sequence for all other codes */ if P00605 > 900 then do ; P00605 = . ; R00605 = '' ; end ; if P00610 > 900 then do ; P00610 = . ; R00610 = '' ; end ; if P00618 > 900 then do ; P00618 = . ; R00618 = '' ; end ; if P00620 > 900 then do ; P00620 = . ; R00620 = '' ; end ; if P00623 > 900 then do ; P00623 = . ; R00623 = '' ; end ; if P00624 > 900 then do ; P00624 = . ; R00624 = '' ; end ; if P00625 > 900 then do ; P00625 = . ; R00625 = '' ; end ; if P00630 > 900 then do ; P00630 = . ; R00630 = '' ; end ; if P00631 > 900 then do ; P00631 = . ; R00631 = '' ; end ; if P00635 > 900 then do ; P00635 = . ; R00635 = '' ; end ; if P00665 > 900 then do ; P00665 = . ; R00665 = '' ; end ; if P00666 > 900 then do ; P00666 = . ; R00666 = '' ; end ; if P00667 > 900 then do ; P00667 = . ; R00667 = '' ; end ; If P80154 = 999 then do ; P80154 = . ; R80154 = '' ; end; if P00605 < -900 then do ; P00605 = . ; R00605 = '' ; end ; if P00610 < -900 then do ; P00610 = . ; R00610 = '' ; end ; if P00618 < -900 then do ; P00618 = . ; R00618 = '' ; end ; if P00620 < -900 then do ; P00620 = . ; R00620 = '' ; end ; if P00623 < -900 then do ; P00623 = . ; R00623 = '' ; end ; if P00624 < -900 then do ; P00624 = . ; R00624 = '' ; end ; if P00625 < -900 then do ; P00625 = . ; R00625 = '' ; end ; if P00630 < -900 then do ; P00630 = . ; R00630 = '' ; end ; if P00631 < -900 then do ; P00631 = . ; R00631 = '' ; end ; if P00635 < -900 then do ; P00635 = . ; R00635 = '' ; end ; if P00665 < -900 then do ; P00665 = . ; R00665 = '' ; end ; if P00666 < -900 then do ; P00666 = . ; R00666 = '' ; end ; if P00667 < -900 then do ; P00667 = . ; R00667 = '' ; end ; if P80154 < -900 then do ; P80154 = . ; R80154 = '' ; end ; if P00605 = 0.0 then do ; if R00605 = 'J' or R00605 = 'O' or R00605 = 'Q' or R00605 = 'Y' then P00605 = . ; else R00605 = '<' ; end ; if P00610 = 0.0 then do ; if R00610 = 'J' or R00610 = 'O' or R00610 = 'Q' or R00610 = 'Y' then P00610 = . ; else R00610 = '<' ; end ; if P00618 = 0.0 then do ; if R00618 = 'J' or R00618 = 'O' or R00618 = 'Q' or R00618 = 'Y' then P00618 = . ; else R00618 = '<' ; end ; if P00620 = 0.0 then do ; if R00620 = 'J' or R00620 = 'O' or R00620 = 'Q' or R00620 = 'Y' then P00620 = . ; else R00620 = '<' ; end ; if P00623 = 0.0 then do ; if R00623 = 'J' or R00623 = 'O' or R00623 = 'Q' or R00623 = 'Y' then P00623 = . ; else R00623 = '<' ; end ; if P00624 = 0.0 then do ; if R00624 = 'J' or R00624 = 'O' or R00624 = 'Q' or R00624 = 'Y' then P00624 = . ; else R00624 = '<' ; end ; if P00625 = 0.0 then do ; if R00625 = 'J' or R00625 = 'O' or R00625 = 'Q' or R00625 = 'Y' then P00625 = . ; else R00625 = '<' ; end ; if P00630 = 0.0 then do ; if R00630 = 'J' or R00630 = 'O' or R00630 = 'Q' or R00630 = 'Y' then P00630 = . ; else R00630 = '<' ; end ; if P00631 = 0.0 then do ; if R00631 = 'J' or R00631 = 'O' or R00631 = 'Q' or R00631 = 'Y' then P00631 = . ; else R00631 = '<' ; end ; if P00635 = 0.0 then do ; if R00635 = 'J' or R00635 = 'O' or R00635 = 'Q' or R00635 = 'Y' then P00635 = . ; else R00635 = '<' ; end ; if P00665 = 0.0 then do ; if R00665 = 'J' or R00665 = 'O' or R00665 = 'Q' or R00665 = 'Y' then P00665 = . ; else R00665 = '<' ; end ; if P00666 = 0.0 then do ; if R00666 = 'J' or R00666 = 'O' or R00666 = 'Q' or R00666 = 'Y' then P00666 = . ; else R00666 = '<' ; end ; if P00667 = 0.0 then do ; if R00667 = 'J' or R00667 = 'O' or R00667 = 'Q' or R00667 = 'Y' then P00667 = . ; else R00667 = '<' ; end ; if P80154 = 0.0 then do ; if R80154 = 'J' or R80154 = 'O' or R80154 = 'Q' or R80154 = 'Y' then P80154 = . ; else R80154 = '<' ; end ; if P00605 = 0.0 then do ; if D00605 > 0 then P00605 = D00605 ; else P00605 = 0.05; end ; if P00610 = 0.0 then do ; if D00610 > 0 then P00610 = D00610 ; else P00610 = 0.05; end ; if P00618 = 0.0 then do ; if D00618 > 0 then P00618 = D00618 ; else P00618 = 0.05; end ; if P00620 = 0.0 then do ; if D00620 > 0 then P00620 = D00620 ; else P00620 = 0.04; end ; if P00623 = 0.0 then do ; if D00623 > 0 then P00623 = D00623 ; else P00623 = 0.25; end ; if P00624 = 0.0 then do ; if D00624 > 0 then P00624 = D00624 ; else P00624 = 0.1; end ; if P00625 = 0.0 then do ; if D00625 > 0 then P00625 = D00625 ; else P00625 = 0.1; end ; if P00630 = 0.0 then do ; if D00630 > 0 then P00630 = D00630 ; else P00630 = 0.02; end ; if P00631 = 0.0 then do ; if D00631 > 0 then P00631 = D00631 ; else P00631 = 0.01; end ; if P00635 = 0.0 then do ; if D00635 > 0 then P00635 = D00635 ; else P00635 = 0.1; end ; if P00665 = 0.0 then do ; if D00665 > 0 then P00665 = D00665 ; else P00665 = 0.05; end ; if P00666 = 0.0 then do ; if D00666 > 0 then P00666 = D00666 ; else P00666 = 0.01; end ; if P00667 = 0.0 then do ; if D00667 > 0 then P00667 = D00667 ; else P00667 = 0.05; end ; if P80154 = 0.0 then do ; if D80154 > 0 then P80154 = D80154 ; else P80154 = 2.4; end ; if P00605 < 0.0 and P00605 NE . then do ; P00605 = P00605 * -1.0 ; if R00605 = 'T' or R00605 = 'W' or R00605 = 'O' then R00605 = 'E' ; else R00605 = '<'; end ; if P00610 < 0.0 and P00610 NE . then do ; P00610 = P00610 * -1.0 ; if R00610 = 'T' or R00610 = 'W' or R00610 = 'O' then R00610 = 'E' ; else R00610 = '<'; end ; if P00618 < 0.0 and P00618 NE . then do ; P00618 = P00618 * -1.0 ; if R00618 = 'T' or R00618 = 'W' or R00618 = 'O' then R00618 = 'E' ; else R00618 = '<'; end ; if P00620 < 0.0 and P00620 NE . then do ; P00620 = P00620 * -1.0 ; if R00620 = 'T' or R00620 = 'W' or R00620 = 'O' then R00620 = 'E' ; else R00620 = '<'; end ; if P00623 < 0.0 and P00623 NE . then do ; P00623 = P00623 * -1.0 ; if R00623 = 'T' or R00623 = 'W' or R00623 = 'O' then R00623 = 'E' ; else R00623 = '<'; end ; if P00624 < 0.0 and P00624 NE . then do ; P00624 = P00624 * -1.0 ; if R00624 = 'T' or R00624 = 'W' or R00624 = 'O' then R00624 = 'E' ; else R00624 = '<'; end ; if P00625 < 0.0 and P00625 NE . then do ; P00625 = P00625 * -1.0 ; if R00625 = 'T' or R00625 = 'W' or R00625 = 'O' then R00625 = 'E' ; else R00625 = '<'; end ; if P00630 < 0.0 and P00630 NE . then do ; P00630 = P00630 * -1.0 ; if R00630 = 'T' or R00630 = 'W' or R00630 = 'O' then R00630 = 'E' ; else R00630 = '<'; end ; if P00631 < 0.0 and P00631 NE . then do ; P00631 = P00631 * -1.0 ; if R00631 = 'T' or R00631 = 'W' or R00631 = 'O' then R00631 = 'E' ; else R00631 = '<'; end ; if P00635 < 0.0 and P00635 NE . then do ; P00635 = P00635 * -1.0 ; if R00635 = 'T' or R00635 = 'W' or R00635 = 'O' then R00635 = 'E' ; else R00635 = '<'; end ; if P00665 < 0.0 and P00665 NE . then do ; P00665 = P00665 * -1.0 ; if R00665 = 'T' or R00665 = 'W' or R00665 = 'O' then R00665 = 'E' ; else R00665 = '<'; end ; if P00666 < 0.0 and P00666 NE . then do ; P00666 = P00666 * -1.0 ; if R00666 = 'T' or R00666 = 'W' or R00666 = 'O' then R00666 = 'E' ; else R00666 = '<'; end ; if P00667 < 0.0 and P00667 NE . then do ; P00667 = P00667 * -1.0 ; if R00667 = 'T' or R00667 = 'W' or R00667 = 'O' then R00667 = 'E' ; else R00667 = '<'; end ; if P80154 < 0.0 and P80154 NE . then do ; P80154 = P80154 * -1.0 ; if R80154 = 'T' or R80154 = 'W' or R80154 = 'O' then R80154 = 'E' ; else R80154 = '<'; end ; run; data dir.&SAS_outputfile; set temp1; /* Apply remaining conversions of remark codes - these can be applied to both modernized and legacy data records For all remaining remark codes that are meant to indicate less than detection, subsitute '<' ; All other codes can remain as they are (innocuous to fluxmaster. */ If R00600 = 'K' or R00600 = 'U' then R00600 = '<' ; /* May want to add code to handle cases remarked as V, which indicates that associated blank had a detect. For now leave as is */ /* Repeat above sequence for all other codes */ if R00605 = 'K' or R00605 = 'U' then R00605 = '<' ; if R00610 = 'K' or R00610 = 'U' then R00610 = '<' ; if R00618 = 'K' or R00618 = 'U' then R00618 = '<' ; if R00620 = 'K' or R00620 = 'U' then R00620 = '<' ; if R00623 = 'K' or R00623 = 'U' then R00623 = '<' ; if R00624 = 'K' or R00624 = 'U' then R00624 = '<' ; if R00625 = 'K' or R00625 = 'U' then R00625 = '<' ; if R00630 = 'K' or R00630 = 'U' then R00630 = '<' ; if R00631 = 'K' or R00631 = 'U' then R00631 = '<' ; if R00635 = 'K' or R00635 = 'U' then R00635 = '<' ; if R00665 = 'K' or R00665 = 'U' then R00665 = '<' ; if R00666 = 'K' or R00666 = 'U' then R00666 = '<' ; if R00667 = 'K' or R00667 = 'U' then R00667 = '<' ; if R80154 = 'K' or R80154 = 'U' then R80154 = '<' ; run;