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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.