I am working with a NOAA weather dataset that codes missing information as some combination of "9". Below is my code to format the data into useful information. In some cases I need to scale variables by a factor of 10 and/or replace "9" values with missing values so that average/max/min calculations are accurate. The problem I am facing is that in some instances, all observations within the group I defined are missing and both the average function and scaling produces an error. The dataset is gigantic, so to test the code I've only been using the first 100 observations. Is there a workaround to produce "missing" for an average of missing values and/or be able to properly scale variables when at least one observation isn't missing? Proc sql inobs=100; create table DAILYCLEAN as select USAF, WBAN, (LAT)/1000 as LAT, (LONG)/1000 as LONG, year, month, day, N(COVER)-NMISS(COVER) as COVERCOUNT, AVG(COVER) as AVGCOVER, N(ELEV)-NMISS(ELEV) as ELEVCOUNT, AVG(CASE ELEV WHEN 9999 THEN NULL ELSE ELEV END) as ELEV, N(TEMP)-NMISS(TEMP) as TEMPCOUNT, AVG(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as AVGTEMP, RANGE(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as RANGETEMP, MAX(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as HITEMP, MIN(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as LOTEMP, N(PRESSURE)-NMISS(PRESSURE) as PRESSURECOUNT, AVG(CASE PRESSURE WHEN 99999 THEN NULL ELSE PRESSURE END)/10 as AVGPRESSURE, MAX(CASE PRESSURE WHEN 99999 THEN NULL ELSE PRESSURE END)/10 as HIPRESSURE, MIN(CASE PRESSURE WHEN 99999 THEN NULL ELSE PRESSURE END)/10 as LOPRESSURE, from "D:\WeatherandZipFiles\COVERNUMERIC.sas7bdat" group by USAF, WBAN, LAT, LONG, year, month, day; quit; run;
... View more