Filling in Omitted Observations in a Time Series Data Set

Working with Time Series Data

Filling in Omitted Observations in a Time Series Data Set

Recall that most SAS/ETS procedures expect input data to be in the standard form, with no omitted observations in the sequence of time periods. When data are missing for a time period, the data set should contain a missing observation, in which all variables except the ID variables have missing values.

You can replace omitted observations in a time series data set with missing observations by merging the data set with a data set containing a complete sequence of dates.

The following statements create a monthly data set, OMITTED, from data lines containing records for an intermittent sample of months. (Data values are not shown.) This data set is converted to a standard form time series data set in four steps.

First, the OMITTED data set is sorted to make sure it is in time order. Second, the first and last date in the data set are determined and stored in the data set RANGE. Third, the data set DATES is created containing only the variable DATE and containing monthly observations for the needed time span. Finally, the data sets OMITTED and DATES are merged to produce a standard form time series data set with missing observations inserted for the omitted records.

   data omitted;
      input date monyy7. x y z;
      format date monyy7.;
   datalines;
   jan1991  ...
   mar1991  ...
   apr1991  ...
   jun1991  ...
    ... etc. ...
   ;
   
   proc sort data=omitted;
      by date;
   run;
   
   data range;
      retain from to;
      set omitted end=lastobs;
      if _n_ = 1 then from = date;
      if lastobs then do;
         to = date;
         output;
         end;
   run;
   
   data dates;
      set range;
      date = from;
      do while( date <= to );
         output;
         date = intnx( 'month', date, 1 );
         end;
      keep date;
   run;
   
   data standard;
      merge omitted dates;
      by date;
   run;

Chapter Contents
Previous
Next
Top