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
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?
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?
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.