topic Re: How to reflect the results on the next ..th observations using the data captured in other field. in SAS Enterprise Guide

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

y3arire_7 — Tue, 16 Apr 2019 09:28:26 GMT

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

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

PeterClemmensen — Tue, 16 Apr 2019 09:50:20 GMT

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;

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

y3arire_7 — Tue, 16 Apr 2019 10:11:55 GMT

Thank you so much! This really works!!

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

PeterClemmensen — Tue, 16 Apr 2019 10:24:35 GMT

Anytime, glad to help 🙂

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

Ksharp — Tue, 16 Apr 2019 14:11:53 GMT

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;

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

y3arire_7 — Thu, 18 Apr 2019 09:41:43 GMT

Thanks! This solution is working too 😉