Calculating Averages of missing values in SQL

[CJRPHD]

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;

[yabwon]

Could you share your error?

I tried to emulate your case bud I see no errors in AVG with all null group:

data x;
  do i = 1,2,3;
    do _N_ = 1 to 3;
      if mod(i,2) then x = floor(10*ranuni(1));
                  else x = .;
      output;
    end;
  end;
run;
proc print;
run;


proc sql;
  select 
    i
  , avg(x) as a1
  , avg(case when x = 9 then . else x end) as a2
  from 
    x
  group by 
    i
  ;
quit;

All the best

Bart

[CJRPHD]

Here is the full LOG:

721 Proc sql inobs=100;
722 create table DAILYCLEAN as
723 select USAF, WBAN, (LAT)/1000 as LAT, (LONG)/1000 as LONG, year, month, day,
724 N(COVER)-NMISS(COVER) as COVERCOUNT,
725 AVG(COVER) as AVGCOVER,
726 N(ELEV)-NMISS(ELEV) as ELEVCOUNT,
727 AVG(CASE ELEV WHEN 9999 THEN NULL ELSE ELEV END) as ELEV,
728 N(TEMP)-NMISS(TEMP) as TEMPCOUNT,
729 AVG(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as AVGTEMP,
730 RANGE(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as RANGETEMP,
731 MAX(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as HITEMP,
732 MIN(CASE TEMP WHEN 9999 THEN NULL ELSE TEMP END)/10 as LOTEMP,
733 N(PRESSURE)-NMISS(PRESSURE) as PRESSURECOUNT,
734 AVG(CASE PRESSURE WHEN 99999 THEN NULL ELSE PRESSURE END)/10 as AVGPRESSURE,
735 MAX(CASE PRESSURE WHEN 99999 THEN NULL ELSE PRESSURE END)/10 as HIPRESSURE,
736 MIN(CASE PRESSURE WHEN 99999 THEN NULL ELSE PRESSURE END)/10 as LOPRESSURE
737 from "D:\WeatherandZipFiles\COVERNUMERIC.sas7bdat"
738 group by USAF, WBAN, LAT, LONG, year, month, day;
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The AVG summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The AVG summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The RANGE summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Expression using division (/) requires numeric types.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Expression using division (/) requires numeric types.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The AVG summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Expression using division (/) requires numeric types.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Expression using division (/) requires numeric types.
ERROR: The following columns were not found in the contributing tables: NULL.
739 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

740 run;

[yabwon · Accepted solution]

So you have your answer:

"ERROR: The following columns were not found in the contributing tables: NULL."

I don't remember which @Kurt_Bremser 's maxim it was, but I'm guessing like first: "Read the log"

You are using NULL and SQL doesn't understand it as null value, use period (.) instead.

All the best

Bart

[CJRPHD]

Ok, this fixed the problem of computations (I will say I had the . originally in my code) but my "count" function wasn't properly recognizing the missing values so I tried null.

I want to count only non-missing values. Is they way I have it in the code incorrect?:

N(PRESSURE)-NMISS(PRESSURE) as PRESSURECOUNT

[CJRPHD]

I know why, I don't know why I didn't realise it earlier. PRESSURE still has the missing values as 99999, that's why my count code isn't working, because it is working. Thanks!

[Kurt_Bremser]

---

@yabwon wrote:

I don't remember which @Kurt_Bremser 's maxim it was, but I'm guessing like first: "Read the log"

---

Actually, it's the second. The first is "Read the Documentation".

But we also have a violation of another maxim here, #34: "Work in Steps". By not building the SQL peacemeal, element by element, the OP created code with so many ERROR messages at once that the most important one was kind of "drowned out" and needed expert's eyes to be discovered.

We also see a violation of Maxim 12, "Make it Look Nice".

There is no consistent use of uppercase/lowercase, in fact most of the code is "shouting at the compiler", which makes it hard to read in the first place. There is no indentation, no use of blanks to separate words and operators, and complex elements like the case blocks are kept in one line, making it hard to see the branches.

Bottom line: this is an example for "how not to do it". That the code fails in a rather spectacular way should not come as a surprise.