SAS Rolling conditional statement (similar to excel)

Question

I have a dataset and I would like to create a rolling conditional statement row by row (not sure what the exact term is called in SAS). I know how to do it in Excel but not sure on how it can be executed on SAS. The following is the dataset and what I would like to achieve.

Data set

----A---- | --Date-- | Amount |
  11111     Jan 2015      1
  11111     Feb 2015      1
  11111     Mar 2015      2
  11111     Apr 2015      2
  11111     May 2015      2
  11111     Jun 2015      1
  11112     Jan 2015      2
  11112     Feb 2015      1
  11112     Mar 2015      1
  11112     Apr 2015      4
  11112     May 2015      3
  11112     Jun 2015      1

I would like to 2 columns by the name of 'X' and 'Frequency' which would provide for each Column 'A' and 'Date' whether the Amount has gone up or down and by how much. See sample output below.

----A---- | --Date-- | Amount | --X-- | Frequency |
  11111     Jan 2015      1       0         0
  11111     Feb 2015      1       0         0
  11111     Mar 2015      2      Add        1
  11111     Apr 2015      2       0         0
  11111     May 2015      2       0         0
  11111     Jun 2015      1      Drop       1
  11112     Jan 2015      2       0         0
  11112     Feb 2015      1      Drop       1
  11112     Mar 2015      1       0         0
  11112     Apr 2015      4      Add        3
  11112     May 2015      3      Drop       1
  11112     Jun 2015      1      Drop       2

Looks like a simple application of the `LAG()` or `DIF()` function. Did you try using that? — Tom, Jan 22 '17 at 18:04

score 0 · Answer 1 · answered Jan 23 '17 at 01:41

Example using Lag1():

    Data A;
    input date monyy7. Y $;
    datalines;
    Jan2015 1       
    Feb2015 1      
    Mar2015 2     
    Apr2015 2       
    May2015 2      
    Jun2015 1     
    Jan2015 2      
    Feb2015 1      
    Mar2015 1       
    Apr2015 4      
    May2015 3      
    Jun2015 1  
    ;

    data B;
    set A;
    lag_y=lag1(Y);
    if lag_y = . then X ='missing';
    if Y = lag_y then X='zero';
    if Y > lag_y and lag_y ^= . then x='add';
    if Y < lag_y then x= 'drop';
    freq= abs(Y-lag_y);
    run;

Output:

   Obs date Y lag_y X freq 
   1 20089 1   missing 
   2 20120 1 1 zero 0 
   3 20148 2 1 add 1 
   4 20179 2 2 zero 0 
   5 20209 2 2 zero 0 
   6 20240 1 2 drop 1 
   7 20089 2 1 add 1 
   8 20120 1 2 drop 1 
   9 20148 1 1 zero 0 
  10 20179 4 1 add 3 
  11 20209 3 4 drop 1 
  12 20240 1 3 drop 2

SAS Rolling conditional statement (similar to excel)

1 Answers1