calculating relative month

[Haritha1]

Hi,

I have an input data as follows.

ID	Flag	date
1	1	1.2014
1	0	2.2014
1	0	3.2014
1	1	4.2014
1	0	5.2014
1	0	6.2014
1	1	7.2014
1	0	8.2014
1	0	9.2014
1	0	10.2014
2	1	2.2014
2	0	3.2014
2	0	4.2014
2	1	5.2014
2	0	6.2014
2	0	7.2014
2	1	8.2014
2	0	9.2014

I need output as follows.

ID	Flag	date	relative_month1	relative_month2	relative_month3
1	1	1.2014	m1
1	0	2.2014	m2
1	0	3.2014	m3
1	1	4.2014	m4	m1
1	0	5.2014	m5	m2
1	0	6.2014	m6	m3
1	1	7.2014	m7	m4	m1
1	0	8.2014	m8	m5	m2
1	0	9.2014	m9	m6	m3
1	0	10.2014	m10	m7	m4
2	1	2.2014	m1
2	0	3.2014	m2
2	0	4.2014	m3
2	1	5.2014	m4	m1
2	0	6.2014	m5	m2
2	0	7.2014	m6	m3
2	1	8.2014	m7	m4	m1
2	0	9.2014	m8	m5	m2

for each ID with change in flag number to 1, I want to create a new varibale (relative month variable) and assign it to m1 and the follow up period should increment by 1. Hope the question is clear.

Any help is appreciated. Thanks alot in Advance.

[Reeza]

Look at the LAG function. 

[Patrick]

@Haritha1

The following code works for your sample data.

data have(drop=_:);
  infile datalines dlm=' ' truncover;
  input ID Flag:$1. _date_str:$7.;
  date=mdy(scan(_date_str,1),'01',scan(_date_str,2));
  format date yymm7.;
datalines;
1 1 1.2014
1 0 2.2014
1 0 3.2014
1 1 4.2014
1 0 5.2014
1 0 6.2014
1 1 7.2014
1 0 8.2014
1 0 9.2014
1 0 10.2014
2 1 2.2014
2 0 3.2014
2 0 4.2014
2 1 5.2014
2 0 6.2014
2 0 7.2014
2 1 8.2014
2 0 9.2014
;
run;

%let rel_mth_arr_size=3;
data want(drop=_:);
  set have;
  by id date;
  array rel_mth_ {&rel_mth_arr_size} 8.;
  if first.id then call missing(of rel_mth_(*));

  do _i=1 to dim(rel_mth_) while(not missing(rel_mth_(_i)));
    rel_mth_(_i)+1;
  end;

  if flag='1' then
    do _i=1 to dim(rel_mth_);
      if missing(rel_mth_(_i)) then 
        do;
          rel_mth_(_i)=1;
          leave;
        end;
    end;
run;

[Haritha1]

Thanks alot for your time. This is very helpful

[Ksharp · Accepted solution]

data have;
  infile datalines dlm=' ' truncover;
  input ID Flag  date : $20.;
datalines;
1 1 1.2014
1 0 2.2014
1 0 3.2014
1 1 4.2014
1 0 5.2014
1 0 6.2014
1 1 7.2014
1 0 8.2014
1 0 9.2014
1 0 10.2014
2 1 2.2014
2 0 3.2014
2 0 4.2014
2 1 5.2014
2 0 6.2014
2 0 7.2014
2 1 8.2014
2 0 9.2014
;
run;
data want;
 set have;
 by id;
 if first.id then do;n=0;x1=.;x2=.;x3=.;end;
 if flag=1 then n+1;
 if n ge 1 then do;x1+1;month1=cats('m',x1);end;
 if n ge 2 then do;x2+1;month2=cats('m',x2);end;
 if n ge 3 then do;x3+1;month3=cats('m',x3);end;
 drop n x1 x2 x3;
run;