Flagging specific cases by ID

[PetePatel]

Hi all,

I have a large dataset with ID, month and 1p_flag (1 or 0) as shown below.

I am trying to create a flag by ID (variable '1_to_0'_to_1), so that for each month I can see if the flag has moved from 1 to 0 to 1 looking at historical months.

I then need to work out the months in between 1 to 0 to 1.

How can I best code this?

I hope this example shows where I want to get to.

Thanks in advance.

ID	Month	1p_flag	1_to_0_to_1	months_between
1	Mar-12	0
1	Apr-12	0
1	May-12	1
1	Jun-12	1
1	Jul-12	0
1	Aug-12	0
1	Sep-12	1	1	3
1	Oct-12	0
2	Apr-12	1
2	May-12	0
2	Jun-12	0
2	Jul-12	1	1	3
2	Aug-12	0
2	Sep-12	0
2	Oct-12	0
2	Nov-12	1	1	4
2	Dec-12	0
2	Jan-13	1	1	2
2	Feb-13	1

[FreelanceReinh · Accepted solution]

Hi @PetePatel,

Try this:

data have;
input ID Month :monyy. _1p_flag;
format Month monyy.;
cards;
1 Mar-12 0
1 Apr-12 0
1 May-12 1
1 Jun-12 1
1 Jul-12 0
1 Aug-12 0
1 Sep-12 1
1 Oct-12 0
2 Apr-12 1
2 May-12 0
2 Jun-12 0
2 Jul-12 1
2 Aug-12 0
2 Sep-12 0
2 Oct-12 0
2 Nov-12 1
2 Dec-12 0
2 Jan-13 1
2 Feb-13 1
;

data want(drop=f0 m1);
set have;
by id month;
retain f0 m1;
if first.id then call missing(f0,m1);
if _1p_flag then do;
  if f0 then do;
    _1_to_0_to_1=1;
    months_between=intck('month',m1,month);
  end;
  m1=month;
  f0=.;
end;
else if m1>. then f0=1;
run;

[Ksharp]

data have;
input ID Month :monyy. _1p_flag;
format Month monyy.;
cards;
1 Mar-12 0
1 Apr-12 0
1 May-12 1
1 Jun-12 1
1 Jul-12 0
1 Aug-12 0
1 Sep-12 1
1 Oct-12 0
2 Apr-12 1
2 May-12 0
2 Jun-12 0
2 Jul-12 1
2 Aug-12 0
2 Sep-12 0
2 Oct-12 0
2 Nov-12 1
2 Dec-12 0
2 Jan-13 1
2 Feb-13 1
;

data want;
retain has_one n 0;
 do i=1 by 1 until(last._1p_flag);
  set have;
  by id _1p_flag notsorted;
  if first.id then do;has_one=0;n=0;end;
  if first._1p_flag and _1p_flag=1 and has_one=1 then do; _1_to_0_to_1=1;months_between=n+1;end;
  output;
  call missing(_1_to_0_to_1,months_between);
 end;
 n=i;
 if _1p_flag=1 then has_one=1;
 drop n i has_one;
run;