Creating running mean with missing values in Stata / SAS

Question

I have a time series of hourly measurement of environmental and meteorological variables (temperature and humidity) over several years. From these hourly values I would like to calculate a 24 hour running mean to create exposure parameter. For this the requirement is that at least 17 of the hourly measurements should be available with no more than 6 hours of consecutive missing values. If more than 6 hourly values are consecutively missing in 24, the data for that specific date is set to missing. How can I implement this in Stata or SAS?

Thanks in advance

Answer 1

It looks like you can create a dummy variable for a "valid" observation using a combination of

• by varname : generate ....,

• egen, and

• lag variables (L.varname, L2.varname... L24.varname...)

Then, create your average using the subset of your data (e.g., yourcommand ... if dummy==1 ...)

Answer 2

Ok here is my attempt. First create some sample data to use:

**
** CREATE ~3 YEARS DAYS OF HOURLY TEMPERATURE DATA
** THIS IS UGLY - IM SURE THERES A BETTER WAY TO DO IT BUT WHATEVER
*;
data tmp;
  pi = constant('pi');
  do year=1 to 3;
    linear_trend = 0.1 * year;
    day = 0;
    do yearly_progress=0 to (pi*2) by (pi/182.5);
      day = day + 1;
      yearly_seasonality = (1 + sin(yearly_progress)) / 2;
      hour = 0;
      day_mod = (ranuni(0)*10);
      do hourly_progress=0 to (pi*2) by (pi/12);
        hourly_seasonality = (1 + sin(hourly_progress)) / 2;
        if hour ne 24 then do;
          temperature = 60*(1+linear_trend) + (20 * yearly_seasonality) + (30 * hourly_seasonality) - day_mod;
          output;
        end;
        hour = hour + 1;
      end;
    end;
  end;
run;


**
** ADD SOME MISSING VALS
** ~ 10% MISSING
** ~ 10 IN A ROW MISSING EVERY 700 OR SO HOURS
*;
data sample_data;
  set tmp;
  if (ranuni(0) < 0.1) or (mod(_n_,710) > 700) then do;
    temperature = .;
  end;
run;

Secondly calculate the moving average for temperature if the requirements are met:

**
** I DONT NORMALLY LIKE USING THE LAG FUNCTION BUT IN THIS CASE ITS IDEAL
*;
data results;
  set sample_data;

  **
  ** POPULATE AN ARRAY WITH THE 24 CURRENT VALUES
  ** BECAUSE WE ARE USING LAG FUNCTION MAKE SURE IT IS NOT WITHIN ANY 
  ** CONDITIONAL IF STATEMENTS
  *;
  array arr [0:23] temperature0-temperature23;
  temperature0  =  lag0(temperature);
  temperature1  =  lag1(temperature);
  temperature2  =  lag2(temperature);
  temperature3  =  lag3(temperature);
  temperature4  =  lag4(temperature);
  temperature5  =  lag5(temperature);
  temperature6  =  lag6(temperature);
  temperature7  =  lag7(temperature);
  temperature8  =  lag8(temperature);
  temperature9  =  lag9(temperature);
  temperature10 = lag10(temperature);
  temperature11 = lag11(temperature);
  temperature12 = lag12(temperature);
  temperature13 = lag13(temperature);
  temperature14 = lag14(temperature);
  temperature15 = lag15(temperature);
  temperature16 = lag16(temperature);
  temperature17 = lag17(temperature);
  temperature18 = lag18(temperature);
  temperature19 = lag19(temperature);
  temperature20 = lag20(temperature);
  temperature21 = lag21(temperature);
  temperature22 = lag22(temperature);
  temperature23 = lag23(temperature);

  **
  ** ITERATE OVER THE ARRAY VARIABLES TO MAKE SURE WE MEET THE REQUIREMENTS
  *;
  available_observations  = 0;
  missing_observations    = 0;
  max_consecutive_missing = 0;
  tmp_consecutive_missing = 0;
  do i=0 to 23;
    if arr[i] eq . then do;
      missing_observations    = missing_observations + 1;
      tmp_consecutive_missing = tmp_consecutive_missing + 1;
      max_consecutive_missing = max(max_consecutive_missing, tmp_consecutive_missing);
    end;
    else do;
      available_observations  = available_observations + 1;        
      tmp_consecutive_missing = 0;
    end;
  end;

  if tmp_consecutive_missing <= 6 and available_observations >= 17 then do;
    moving_avg = mean(of temperature0-temperature23);
  end;
run;

Answer 3

A Stata solution:

1. Use tssmooth ma myvar_ma = myvar, w(24) to create a moving average. Missings will be ignored.

2. Create an indicator gen ismiss = missing(myvar)

3. Use tssmooth ma ismiss_ma = ismiss, w(24) to create a moving average of the indicator.

4. replace myvar_ma = . if ismiss_ma > (7/24)

(At least 17/24 must be present, so 7 or fewer missing is acceptable, but 8 or more is not.

EDIT. tsegen from SSC now offers a simple approach to this kind of problem. You can specify the minimum acceptable number of non-missing values in the window directly in the command syntax.

Answer 4

For general moving average calculations, using PROC EXPAND is the easiest method (you need ETS licenced to use this procedure). For example, the code below will calculate a 24 period moving average and set the first 16 observations to missing. However, to comply with the rest of your criteria you would still need to run a data step afterwards, along the lines of Rob's code, so you may as well perform all the calculations within that step.

proc expand data=sample_data out=mov_avg_results;
convert temperature=mean_temp / method=none transformout=(movave 24 trimleft 17);
run;