calculating relative month

Solved
Occasional Contributor
Posts: 12

calculating relative month

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.

Accepted Solutions
Solution
‎07-01-2017 09:59 AM
Super User
Posts: 10,848

Re: calculating relative month

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

All Replies
Super User
Posts: 23,980

Re: calculating relative month

Look at the LAG function.

Posts: 4,779

Re: calculating relative month

@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;
``````
Occasional Contributor
Posts: 12

Re: calculating relative month

Solution
‎07-01-2017 09:59 AM
Super User
Posts: 10,848

Re: calculating relative month

``````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;
``````
☑ This topic is solved.