From: HIS@NIHCU.BITNET (Howard Schreier)
Newsgroups: bit.listserv.sas-l
Subject: SQL for Moving Averages
Date: 4 Nov 92 22:40:09 GMT
Reply-To: Howard Schreier <HIS@NIHCU.BITNET>
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU

CONTENT:  Follow-up
SUMMARY:  SQL can be tricked into lead/lag applications
REL/PLTF: 6.06+

I'm  pulling  together  two  recent  threads  here:   moving
averages and lags/leads.  A centered moving average requires
looking ahead as well as back,  and  cannot  be  implemented
with  simple LAG references.  As I mentioned a few days ago,
SQL is not hopeless for this kind of thing, even  though  it
does  not allow anything based on the order of the rows in a
table (such as LAGs or FIRST.BY flags).

Here is a test data set.  The BY groups are,  intentionally,
not completely sorted.

   data test; input by_1 $ by_2 $ value; cards;
   A x 1
   A x 1
   A x 2
   A x 2
   B x 3
   B x 3
   B x 4
   B x 4
   B y 5
   B y .
   B y 6
   B y 6
   B y 7
   B x 8
   A x 8
   ;

The problem is to preserve the observations,  in  the  order
given,  and  add  a  moving  average  of VALUE (spanning the
preceding,  current,  and   following   observations);   the
calculations  are  to  be  compartmentalized  within each BY
group, and missing values are to propagate.

The first thing is to add serial numbers  for  both  the  BY
groups and the individual observations:

   data test;
   set test;
   by by_1 notsorted by_2 notsorted;
   __rownum + 1;
   if first.by_2 then __grpnum + 1;

SQL now has what it needs:

   proc sql;
   select distinct test.by_1, test.by_2, test.value,
                   case n(lead_lag.value)
                    when 3 then mean(lead_lag.value)
                    else .
                    end               as move_avg
    from test, test as lead_lag
    where test.__grpnum=lead_lag.__grpnum
     and test.__rownum-1 <= lead_lag.__rownum <= test.__rownum+1
    group by test.__rownum
    order by test.__rownum
    ;

The first three  columns  SELECTed  are  just  the  original
variables.   The  FROM  clause  joins  (crosses)  TEST  with
itself.  The  first  WHERE  condition  serves  to  keep  the
BY-1/BY-2  groups  separate,  and  the second implements the
span of the moving average (+/- 1).   The  GROUP  BY  clause
causes the summary statistics (within the CASE construct) to
be done separately for each  original  row.   If  there  are
three values (as when the original row is *not* the first or
last in a BY-1/BY-2 group and there are no missing  values),
an average is computed; otherwise, a missing value is loaded
instead.  The DISTINCT keyword eliminates the duplicate rows
which otherwise would result from the join operation.

The results:

               BY_1      BY_2         VALUE  MOVE_AVG
               --------------------------------------
               A         x                1         .
               A         x                1  1.333333
               A         x                2  1.666667
               A         x                2         .
               B         x                3         .
               B         x                3  3.333333
               B         x                4  3.666667
               B         x                4         .
               B         y                5         .
               B         y                .         .
               B         y                6         .
               B         y                6  6.333333
               B         y                7         .
               B         x                8         .
               A         x                8         .

This basic  approach  should  work  for  a  lot  of  similar
problems.  If the data set is sorted on the BY variables and
contains a suitable sequence  variable,  the  pre-processing
step  would  not  be  necessary  and the SQL should appear a
little more natural.


/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
\   Howard Schreier, U.S. Dept. of Commerce, Washington    /
/                     MVS 5.18 & 6.07                      \
\   Voice: (202) 377-4180        BITNET: HIS@NIHCU         /
/   Fax:   (202) 377-4614      INTERNET: HIS@CU.NIH.GOV    \
\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU