topic Re: Patterns in Audit Data in SAS Procedures

Patterns in Audit Data

SteveNZ — Tue, 09 Dec 2014 02:33:23 GMT

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

Re: Patterns in Audit Data

DaveBirch — Tue, 09 Dec 2014 07:27:08 GMT

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?

Re: Patterns in Audit Data

SteveNZ — Tue, 09 Dec 2014 19:04:18 GMT

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