/*****************************************************************************************************************

SAS file name: rollingstats_sql
File location: 
_________________________________________________________________________________________________________________

Purpose: To demonstrate how to use PROC SQL to create rolling statistics in SAS
Author: Peter Clemmensen
Creation Date: 22/04/2019

This program supports the example page "Create Rolling Statistics With PROC SQL in SAS" on SASnrd.com

*****************************************************************************************************************/

/* Rolling statistics with PROC SQL */
proc sort data=sashelp.stocks out=stocks;
   by stock date;
run;

/* Create 1 year backwards moving average */
proc sql;
   create table test1 as
   select *, 
          (select mean(close) from stocks 
             where stock=a.stock
               and (intnx('month', a.Date, -11, 'b') le Date le a.Date)) 
                 as mean1year format=dollar8.2
   from stocks as a;
quit;

/* Plot the created moving average */
title '1 Year Backwards Moving Average with PROC SQL';
proc sgplot data=test1;
   where Stock='IBM';
   series x=date y=mean1year;
run;
title;

/* Count number of times that the stock price is above 100 in the last year */
proc sql;
   create table test2 as
   select *, 
          (select n(close) from stocks 
              where stock=a.stock
                and (intnx('month', a.Date, -11, 'b') le Date le a.Date
                  and close > 100))
                     as countover100
   from stocks as a;
quit;