/* other impairment list */ proc contents data = ir2014.ir; run; proc sql; select distinct IMPAIRMENT_CATEGORY,method, parameter, tceq_cause, INTEGRATED_SUPPORT_CODE, current_303d_description from ir2014.ir; quit; proc sql; create table otherimp as select distinct auid from ir2014.hgac where other is not missing; quit; proc sql; create table bsr2016.other_impairment as select x.*, y.method, y.parameter, y.impairment_category, y.current_303d_description, y.tceq_cause, integrated_support_code from otherimp as x left join ir2014.ir as y on x.auid=y.au_id; quit; data other1; set bsr2016.other_impairment; where strip(method) in ('Acute Toxic Substances in water' 'Acute Toxicity tests in whole sediment' 'Chronic Toxic Substances in water' 'DSHS Advisories, Closures, and Risk Assessments' 'DSHS Shellfish Harvesting Maps' 'Fish Community' 'HH Bioaccumulative Toxics in water' 'Habitat' 'High pH' 'LOE Toxic Sediment condition' 'Low pH' 'Macrobenthic Community' 'Surface Water HH criteria for PWS average' 'Toxic Substances in sediment' 'Water Temperature'); run; data other2; set other1; where strip(impairment_category) NE ''; run; data other3; set other1; where strip(integrated_support_code) = 'CN'; run; data other4; set other2 other3; run; data other5; set other4; if substr(tceq_cause,1,3) in ('PCB' 'dio') then delete; run; data bsr2016.other_impairments_concerns; set other5; if substr(auid,1,4) in ('2482' '2483') then delete; if substr(auid,5,2) = 'OW' then delete; run; proc export data = bsr2016.other_impairments_concerns outfile = "G:\CE\Databases\Clean_Rivers_Program\SAS_Data_Processing\Project Code and Data\BSR 2016\other impairments\other impairments.xls" dbms = excel replace; run; proc export data = bsr2016.other_impairments_concerns outfile = "G:\CE\Databases\Clean_Rivers_Program\SAS_Data_Processing\Project Code and Data\BSR 2016\other impairments\other impairments.dbf" dbms = dbf; run; ods rtf; proc sql; select auid, method, parameter,tceq_cause, compress(integrated_support_code)from bsr2016.other_impairments_concerns order by auid; quit; ods rtf close;