topic Re: Calculating differences from several columns to identify interruptions in SAS Programming

Calculating differences from several columns to identify interruptions

Dynamike — Thu, 11 Oct 2018 07:24:40 GMT

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

Re: Calculating differences from several columns to identify interruptions

RW9 — Thu, 11 Oct 2018 07:49:08 GMT

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.

Re: Calculating differences from several columns to identify interruptions

Dynamike — Thu, 11 Oct 2018 08:05:13 GMT

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

Re: Calculating differences from several columns to identify interruptions

s_lassen — Thu, 11 Oct 2018 08:24:39 GMT

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;

Re: Calculating differences from several columns to identify interruptions

RW9 — Thu, 11 Oct 2018 08:46:10 GMT

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

Re: Calculating differences from several columns to identify interruptions

Dynamike — Thu, 11 Oct 2018 12:49:48 GMT

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