Help using Base SAS procedures

Patterns in Audit Data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Patterns in Audit Data

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


example.jpg

Accepted Solutions
Solution
‎12-09-2014 02:27 AM
Contributor
Posts: 25

Re: Patterns in Audit Data

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?

View solution in original post


All Replies
Solution
‎12-09-2014 02:27 AM
Contributor
Posts: 25

Re: Patterns in Audit Data

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?

Frequent Contributor
Posts: 87

Re: Patterns in Audit Data

Posted in reply to DaveBirch

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 280 views
  • 1 like
  • 2 in conversation