topic Re: Looking for a repeating sequence/pattern across multiple columns in SAS Programming

Looking for a repeating sequence/pattern across multiple columns

ChrisJones92 — Tue, 27 Apr 2021 20:59:21 GMT

Hi everyone,

I've been scratching my head with this all day. I can think of solutions that apply to individual cases, but not something dynamic enough to adapt to different data.

Essentially, I have a dataset that includes changes made to a policy. I'm looking to identify those that alternate between two names regularly. The problem is, the number of changes and the permutations of these can be flexible.

For example, I'd want to include these policies, as their names at some point alternate between two:

1	2	3	4	5	6	7
Jones	Jones	Smith	Jones	Smith	Jones
Heath	Davis	Heath	Davis
Wallis	King	Wallis	King	Wallis	Wallis	King
Matthews	Matthews	Matthews	Stewart	Matthews	Stewart	Matthews

I wouldn't want to include these (below) as they do not regularly alternate:

1	2	3	4	5	6	7
Jones	Jones	Jones	David	David	David	David
Smith	Lucas	Lucas	Lucas	Lucas	Lucas
Williams	Williams	Williams	Williams	Jones

I'm not sure I've explained this particularly well, but I hope this makes sense. I'm trying to identify those that at any point exhibit a pattern of alternating between two names. I've been given the threshold of switching from one name to another and back again at any point in the history of changes.

Thanks in advance for reading and for any advice.

Re: Looking for a repeating sequence/pattern across multiple columns

ChrisNZ — Wed, 28 Apr 2021 00:36:09 GMT

> alternate between two names regularly

How do you define regularly?

You say that ABAB qualifies, but AB doesn't. What about ABA? Or ABCAB?

A strategy would be:

1. Put values in an array (let's call it full array, optional)

2. Remove consecutive duplicates (let's call it dedupe array)

3. Create an array full deduplicated (let's call it list array)

4. Count the number of time each value in list appears in dedupe and apply the rules you've set.

Re: Looking for a repeating sequence/pattern across multiple columns

japelin — Wed, 28 Apr 2021 00:36:46 GMT

How about a code like this?

data have;
  length name1-name7 $20;
  infile datalines dsd missover;
input name1--name7;
datalines;
Jones,Jones,Smith,Jones,Smith,Jones, 
Matthews,Matthews,Matthews,Stewart,Matthews,Stewart,Matthews
Jones,Jones,Jones,David,David,David,David
Heath,Davis,Heath,Davis, , , 
Wallis,King,Wallis,King,Wallis,Wallis,King
Williams,Williams,Williams,Williams,Jones, , 
Smith,Lucas,Lucas,Lucas,Lucas,Lucas, 
;
run;
data want;
  set have;
  array a_name{7} name1-name7;
  flg=0;
  do i=2 to dim(a_name)-2;
    if a_name{i} ^= '' then do;
      if a_name{i-1}=a_name{i+1} and
         a_name{i}=a_name{i+2} and
         a_name{i}^=a_name{i+1} then do;
        flg=1;
        if flg then leave;
      end;
    end;
  end;
  if flg;
  drop i flg;
run;

Re: Looking for a repeating sequence/pattern across multiple columns

ChrisJones92 — Wed, 28 Apr 2021 13:22:35 GMT

Amazing! This worked perfectly. Many thanks for your help!