DATA Step, Macro, Functions and more

calculating relative month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

calculating relative month

Hi,

 

I have an input data as follows.

 

IDFlagdate
111.2014
102.2014
103.2014
114.2014
105.2014
106.2014
117.2014
108.2014
109.2014
1010.2014
212.2014
203.2014
204.2014
215.2014
206.2014
207.2014
218.2014
209.2014

 

I need output as follows.

 

IDFlagdaterelative_month1relative_month2relative_month3
111.2014m1  
102.2014m2  
103.2014m3  
114.2014m4m1 
105.2014m5m2 
106.2014m6m3 
117.2014m7m4m1
108.2014m8m5m2
109.2014m9m6m3
1010.2014m10m7m4
212.2014m1  
203.2014m2  
204.2014m3  
215.2014m4m1 
206.2014m5m2 
207.2014m6m3 
218.2014m7m4m1
209.2014m8m5m2

 

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,046

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;

View solution in original post


All Replies
Super User
Posts: 19,872

Re: calculating relative month

Look at the LAG function. 

Respected Advisor
Posts: 4,173

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

Thanks alot for your time. This is very helpful

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 165 views
  • 2 likes
  • 4 in conversation