How to assign a value with conditions for two variables and capturing 12 months observations?

[y3arire_7]

Hihi, anyone here understand and could share with me the solutions to resolve the below queries?

1) How to assign the number to the first "1" identified under "Active" variable by "Customer ID"?

2) How to produce the "Desired result" by identifying the first "1" and creating the column of "Desired Result" that captures the preceding 12 months observations?

Thank you so much for shedding the light for these ❤️

Period	Customer ID	Active	Self-created Number	Desired Result
201304	1	0		1
201305	1	0		1
201306	1	0		1
201307	1	0		1
201308	1	0		1
201309	1	0		1
201310	1	1	12	1
201311	1	0
201312	1	0
201401	1	0
201402	1	0
201403	1	0
201404	1	0
201405	1	0
201406	1	0
201407	1	0
201408	1	1
201409	1	0
201410	1	0
201411	1	0
201412	1	0
201304	8	0
201305	8	0
201306	8	0
201307	8	0		1
201308	8	0		1
201309	8	0		1
201310	8	0		1
201311	8	0		1
201312	8	0		1
201401	8	0		1
201402	8	0		1
201403	8	0		1
201404	8	0		1
201405	8	0		1
201406	8	1	12	1
201407	8	1
201408	8	1
201409	8	0
201410	8	0
201411	8	0
201412	8	0

[PeterClemmensen]

So the Self Created Number, could that be other values than 12 or is it always 12?

[y3arire_7]

Hi Draycut, for this scenario, it would be always 12.

[PeterClemmensen · Accepted solution]

Ok. This gives you what you want. Ask if you have questions about the code 🙂

data have;
input Period :yymmn6. Customer_ID Active;
format Period yymmn6.;
datalines;
201304 1 0
201305 1 0
201306 1 0
201307 1 0
201308 1 0
201309 1 0
201310 1 1
201311 1 0
201312 1 0
201401 1 0
201402 1 0
201403 1 0
201404 1 0
201405 1 0
201406 1 0
201407 1 0
201408 1 1
201409 1 0
201410 1 0
201411 1 0
201412 1 0
201304 8 0
201305 8 0
201306 8 0
201307 8 0
201308 8 0
201309 8 0
201310 8 0
201311 8 0
201312 8 0
201401 8 0
201402 8 0
201403 8 0
201404 8 0
201405 8 0
201406 8 1
201407 8 1
201408 8 1
201409 8 0
201410 8 0
201411 8 0
201412 8 0
;

data want(drop=from to);
   do until (last.Customer_ID);
      set have;
      by Customer_ID;
      if Active=1 and to=. then do;
         to=Period;
         from=intnx('month', Period, -11, 's');
      end;
   end;

   do until (last.Customer_ID);
      set have;
      by Customer_ID;
      Desired_Result=0;
      if from le Period le to then Desired_Result=1;
      output;
   end;
run;

[y3arire_7]

Hi Thanks for the code. It works; however, it did not assign the figure to only the preceding 12 months but all the preceding months with the first Active = 1 identified 😃

[Ksharp]

data have;
input Period :yymmn6. Customer_ID Active;
format Period yymmn6.;
datalines;
201304 1 0
201305 1 0
201306 1 0
201307 1 0
201308 1 0
201309 1 0
201310 1 1
201311 1 0
201312 1 0
201401 1 0
201402 1 0
201403 1 0
201404 1 0
201405 1 0
201406 1 0
201407 1 0
201408 1 1
201409 1 0
201410 1 0
201411 1 0
201412 1 0
201304 8 0
201305 8 0
201306 8 0
201307 8 0
201308 8 0
201309 8 0
201310 8 0
201311 8 0
201312 8 0
201401 8 0
201402 8 0
201403 8 0
201404 8 0
201405 8 0
201406 8 1
201407 8 1
201408 8 1
201409 8 0
201410 8 0
201411 8 0
201412 8 0
;
data want;
 set have;
 by Customer_ID;
 retain want 1 found 0;
 if first.Customer_ID then do; want=1; found=0; end;
 if found then want=0;
 if active=1 and not found then found=1;
 drop found;
run;

[y3arire_7]

Hi Ksharp thanks for the sharing.
This code works; however, it did not assign the figure to only the preceding 12 months but all the preceding months with the first Active = 1 identified 😃

[Ksharp]

OK. Then use @PeterClemmensen  code .