topic How to assign a value with conditions for two variables and capturing 12 months observations? in SAS Enterprise Guide

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

y3arire_7 — Fri, 26 Apr 2019 04:58:42 GMT

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

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

PeterClemmensen — Fri, 26 Apr 2019 08:11:25 GMT

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

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

y3arire_7 — Fri, 26 Apr 2019 08:14:42 GMT

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

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

PeterClemmensen — Fri, 26 Apr 2019 08:52:31 GMT

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;

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

Ksharp — Fri, 26 Apr 2019 13:20:33 GMT

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;

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

y3arire_7 — Mon, 29 Apr 2019 11:55:59 GMT

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 😃

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

y3arire_7 — Mon, 29 Apr 2019 11:56:32 GMT

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 😃

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

Ksharp — Mon, 29 Apr 2019 14:02:33 GMT

OK. Then use @PeterClemmensen code .