How to reflect the results on the next ..th observations using the data captured in other field.

[y3arire_7]

Hi Is anyone here could share how to write the code to reflect the results "Active Status" as below?

I would like to have a field to reflect whether the customer is still active based on the subscription term ( month).

For example, when the subscription term is 24 months, the active status will be flagged as "Y" for the next 23 months.

Really really appreciate if anyone could shed the light on this question.

Thank you so much!

YYYYMM	Customer	Subscription Term	Active Status
201712	1	1	Y
201801	1	3	Y
201802	1	24	Y
201803	1		Y
201804	1		Y
201805	1		Y
201806	1		Y
201807	1		Y
201808	1		Y
201809	1		Y
201810	1		Y
201811	1		Y
201812	1		Y
201901	1		Y
201902	1		Y
201903	1		Y
201904	1		Y
201905	1		Y
201906	1		Y
201907	1		Y
201908	1		Y
201909	1		Y
201910	1		Y
201911	1		Y
201912	1		Y
202001	1		Y
201712	3
201801	3
201802	3
201803	3	6	Y
201804	3		Y
201805	3		Y
201806	3	7	Y
201807	3		Y
201808	3		Y
201809	3		Y
201810	3		Y
201811	3		Y
201812	3		Y
201901	3
201902	3
201903	3
201904	3
201905	3
201906	3
201907	3
201908	3
201909	3
201910	3

[PeterClemmensen · Accepted solution]

Do something like this

data have;
input date:yymmn6. Customer $ SubscriptionTerm;
infile datalines dsd missover;
datalines;
201712,1,1
201801,1,3
201802,1,24
201803,1, 
201804,1, 
201805,1, 
201806,1, 
201807,1, 
201808,1, 
201809,1, 
201810,1, 
201811,1, 
201812,1, 
201901,1, 
201902,1, 
201903,1, 
201904,1, 
201905,1, 
201906,1, 
201907,1, 
201908,1, 
201909,1, 
201910,1, 
201911,1, 
201912,1, 
202001,1, 
201712,3, 
201801,3, 
201802,3, 
201803,3,6
201804,3, 
201805,3, 
201806,3,7
201807,3, 
201808,3, 
201809,3, 
201810,3, 
201811,3, 
201812,3, 
201901,3, 
201902,3, 
201903,3, 
201904,3, 
201905,3, 
201906,3, 
201907,3, 
201908,3, 
201909,3, 
201910,3, 
;

data want(drop=_temp);
   do until (last.Customer);
      set have;
      by Customer;
      if not missing(SubscriptionTerm) then do;
         _temp=SubscriptionTerm;
         ActiveStatus='Y';
      end;
      else do;
         if _temp>0 then ActiveStatus='Y';
         else ActiveStatus=' ';
      end;
      _temp=_temp-1;
      output;
   end;
   format date yymmn6.;
run;

[y3arire_7]

Thank you so much! This really works!!

[PeterClemmensen]

Anytime, glad to help 🙂

[Ksharp]

I know you got the answer, But I want give it a try because I love this kind of question.

data have;
input date:yymmn6. Customer $ SubscriptionTerm;
infile datalines dsd missover;
datalines;
201712,1,1
201801,1,3
201802,1,24
201803,1, 
201804,1, 
201805,1, 
201806,1, 
201807,1, 
201808,1, 
201809,1, 
201810,1, 
201811,1, 
201812,1, 
201901,1, 
201902,1, 
201903,1, 
201904,1, 
201905,1, 
201906,1, 
201907,1, 
201908,1, 
201909,1, 
201910,1, 
201911,1, 
201912,1, 
202001,1, 
201712,3, 
201801,3, 
201802,3, 
201803,3,6
201804,3, 
201805,3, 
201806,3,7
201807,3, 
201808,3, 
201809,3, 
201810,3, 
201811,3, 
201812,3, 
201901,3, 
201902,3, 
201903,3, 
201904,3, 
201905,3, 
201906,3, 
201907,3, 
201908,3, 
201909,3, 
201910,3, 
;
data have;
 set have;
 by Customer;
 if first.Customer then n=0;
 n+1;
run;

data key;
 set have(where=(SubscriptionTerm is not missing) keep=Customer SubscriptionTerm n);
 output;
 do i=1 to SubscriptionTerm-1 ;
  n=n+1;output;
 end;
 keep Customer n;
run;
data want;
 if _n_=1 then do;
   if 0 then set key;
   declare hash h(dataset:'key');
   h.definekey('Customer','n');
   h.definedone();
 end;
set have;
if h.check()=0 then flag='Y';
run; 

[y3arire_7]

Thanks! This solution is working too 😉