topic Re: calculating relative month in SAS Programming

calculating relative month

Haritha1 — Sat, 01 Jul 2017 01:50:33 GMT

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.

Re: calculating relative month

Reeza — Sat, 01 Jul 2017 02:57:54 GMT

Look at the LAG function.

Re: calculating relative month

Patrick — Sat, 01 Jul 2017 03:06:57 GMT

@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;

Re: calculating relative month

Ksharp — Sat, 01 Jul 2017 05:38:23 GMT

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;

Re: calculating relative month

Haritha1 — Sat, 01 Jul 2017 13:59:06 GMT

Thanks alot for your time. This is very helpful