Patterns in Audit Data

[SteveNZ]

Hi There,

I need to identify audit actions where only a pattern of actions occurred (all other actions discarded).

The data is sorted by ClientID and DateAdd and there is a screen code for each line (Scr_Code). I need to pull out only cases where the order of screen codes goes ACC, ACC, WITHDR, CLOSSED

I've attached a screen dump of an example with the pattern highlighted - I would only want this pattern returned, nothing else

I know it probably could be done with multiple lags but is there a more elegant way? The table holds millions of records.

Many, many thanks in advance.

Steve

[DaveBirch · Accepted solution]

I think lags are actually the most elegant way.

data want;

   set have;

   by ClientID DateAdd /*is there a Seqence Id for the ScreenCodes*/;

   Prev_ScreenCode_3 = log3(ScreenCode);

   Prev_ScreenCode_2 = log2(ScreenCode);

   Prev_ScreenCode_1 = log1(ScreenCode);

   if first.DateAdd then do;

      Prev_ScreenCode_3 = '';

      Prev_ScreenCode_2 = '';

      Prev_ScreenCode_1 = '';

      end;

   if ScreenCode eq 'CLOSSED' and Prev_ScreenCode_1 eq 'WITHDR'

      and Prev_ScreenCode_2 eq 'ACC' and Prev_ScreenCode_3 eq 'ACC'

      then output have;

run;

Are there other variables that need to be considered?  Do you *need* the pattern as 4 rows?

[SteveNZ]

Hi Dave,

Worked a treat and yes there was a sequence id which I ended up using. I never realised you could put a number beside the lag to do it in one step - I thought it was going to be a nightmare of many lags!

Thank you very much for your help, really appreciated.

cheers

Steve