Calculating differences from several columns to identify interruptions

[Dynamike]

Hi all,

my table looks like this:

ID Name       COL1 COL2 COL3 COL4 COL5  COL6  ....COL78

x   MONTH    1         2         3          4        5        6

y   MONTH    4         5         6          .         8        9

z   MONTH    12       1         2           3        .        .

ID is the variable for the patient and COL consists of the number of the month of a year (12 is December, 1 January....).

So what i want to know is the following:

Are there any interruptions for e.g. patient y regarding the therapy?

And of course there is an interruption (see COL4).

I think first of all i have to calculate the differences between each variable COL2-COL1 COL3-COL2 .... COL78-COL77.

And then if this difference does not equals 1 there is an interruption (but what is if its December to January 1-12=-11. this also an valid value or if the missing is at the end (patient z) this not an interruption, this the end of the therapy (which is ok)).

Do you have any idea to solve this with an macro or something like this (i have no marco experience at all).

Do you need more information to help me?

Thank you for ur effort!

Mike

[RW9]

You can use an array and a flag to ascertain this, something like:

data want;
   set have;
   array col{78};
   flag=0;
   missed=0;
   do i=1 to 78;
      if col{i}=. then flag=1;
      else if col{i} ne . and flag=1 then do;
        flag=0;
        missed=sum(missed,1);
     end;
  end;
run; 

This should count any instances where . occurs (sets flag) then if a number happens it means there was a gap so add to count.

[Dynamike]

Thank you very much for ur quick answer.


So am i right?: flag=1 could also signify the end of the therapy, BUT if there is missed > 0 there is at least one interruption in between.


best regards

Mike

[RW9]

Flag is just an on/off switch to indicate if a . has occured so that missings with a value afterwards has happened. 

[s_lassen · Accepted solution]

Something like this, perhaps?

data want;
  set have;
  array cols col1-col78;
  interrupt=n(of cols(*))>0 and missing(col1);
  do _N_=2 to n(of cols(*)) while(not interrupt);
    interrupt=mod(cols(_N_),12) ne mod(cols(_N_-1)+1,12);
    end;
run;

Or, if you need to find the place where the first interruption is:

data want;
  set have;
  array cols col1-col78;
  interrupt=n(of cols(*))>0 and missing(col1);
  do _N_=2 to n(of cols(*)) while(not interrupt);
    if mod(cols(_N_),12) ne mod(cols(_N_-1)+1,12) then
      interrupt=_N_;
    end;
run;

[Dynamike]

Thank you this works very well!

Now  i know there is an interruption and when.

Is it possible to add an information how long the interruption took place?

Thank you in advance!

Mike