topic Re: Retain a flag for X number of months in SAS Programming

Retain a flag for X number of months

PetePatel — Wed, 19 Jul 2017 13:39:01 GMT

Hello SAS Experts,

I have a dataset with >10,000 ID's and observation months ranging from 2007 to 2017.

Please see a sample dataset with two ID's below.

When 'trigger=1' I have created 'ED' (effective duration) which defines how many months the 'desired' flag should be switched on for each unqiue ID. The 'desired' flag is what I am trying to get to and should not be overwitten by the next observation (for example ID=2 at Oct-15, the 'desired' flag should remain switched on due to ED=4 at Aug-15).

In addition, if the ED is greater than the number of observations for that account the 'desired' flag should continue to be switched on until the last observation without producing an error.

I have been trying a combination of retain statements and lag functions but struggling to get to the 'desired' solution.

Any help would be greatly appreciated.

Thanks,

Pete

ID	Month	flag	ED	desired
1	Jan-12	0	0	0
1	Feb-12	0	0	0
1	Mar-12	0	0	0
1	Apr-12	1	3	1
1	May-12	0	0	1
1	Jun-12	0	0	1
1	Jul-12	0	0	0
1	Aug-12	1	2	1
1	Sep-12	0	0	1
1	Oct-12	0	0	0
1	Nov-12	0	0	0
1	Dec-12	0	0	0
2	Jul-15	0	0	0
2	Aug-15	1	4	1
2	Sep-15	1	1	1
2	Oct-15	1	0	1
2	Nov-15	0	0	1
2	Dec-15	0	0	0
2	Jan-16	0	0	0
2	Feb-16	0	0	0
3	Jan-16	1	10	1
3	Feb-16	0	0	1
3	Mar-16	0	0	1
3	Apr-16	0	0	1

Re: Retain a flag for X number of months

art297 — Wed, 19 Jul 2017 14:22:05 GMT

Here is one way:

data want (drop=hold_ED);
  set have;
  retain hold_ED;
  hold_ED=max(ED,hold_ED);
  if hold_ED gt 0 then desired=1;
  else desired=0;
  hold_ED=hold_ED-1;
run;

Art, CEO, AnalystFinder.com

Re: Retain a flag for X number of months

Ksharp — Wed, 19 Jul 2017 14:33:24 GMT

data have;
infile cards expandtabs truncover;
input ID	Month $ 	flag	ED	;
cards;
1	Jan-12	0	0	0
1	Feb-12	0	0	0
1	Mar-12	0	0	0
1	Apr-12	1	3	1
1	May-12	0	0	1
1	Jun-12	0	0	1
1	Jul-12	0	0	0
1	Aug-12	1	2	1
1	Sep-12	0	0	1
1	Oct-12	0	0	0
1	Nov-12	0	0	0
1	Dec-12	0	0	0
2	Jul-15	0	0	0
2	Aug-15	1	4	1
2	Sep-15	1	1	1
2	Oct-15	1	0	1
2	Nov-15	0	0	1
2	Dec-15	0	0	0
2	Jan-16	0	0	0
2	Feb-16	0	0	0
3	Jan-16	1	10	1
3	Feb-16	0	0	1
3	Mar-16	0	0	1
3	Apr-16	0	0
;
run;
data temp;
 set have;
 by id;
 if first.id then n=0;
  n+1;
run;
data k;
 set temp;
 if ed ;
run;
data key;
 set k;
 retain desired 1;
 _n=n;
 do i=0 to ed-1;
  n=_n+i;output;
 end;
 keep id desired n;
run;
proc sort data=key out=unique nodupkey;
 by id n;
run;
data want;
 merge temp(in=ina) unique;
 by id n;
 if ina;
run;

Re: Retain a flag for X number of months

PetePatel — Wed, 19 Jul 2017 15:45:22 GMT

Hi art291,

Thank you for your reply.

The solution works great in principle but does not reset when moving onto the next ID.

For example, ID=3 at Jan-16 ED=10, where 'desired' flag should be switched on up to and including Apr-16 only. ID=4 should then reset and start the process again.

I tried retaining by ID but currently unable to get it to work.

3	Jan-16	1	10	1
3	Feb-16	0	0	1
3	Mar-16	0	0	1
3	Apr-16	0	0	1
4	Jan-12	0	0	0
4	Feb-12	1	2	1
4	Mar-12	0	0	1
4	Apr-12	0	0	0
4	May-12	0	0	0

Re: Retain a flag for X number of months

art297 — Wed, 19 Jul 2017 15:56:50 GMT

@PetePatel: The following will account for changes in ID:

data want (drop=hold_ED);
  set have;
  by id;
  retain hold_ED;
  if first.id then hold_ED=ED;
  else hold_ED=max(ED,hold_ED);
  if hold_ED gt 0 then desired=1;
  else desired=0;
  hold_ED=hold_ED-1;
run;

Art, CEO, AnalystFinder.com

Re: Retain a flag for X number of months

PetePatel — Wed, 19 Jul 2017 16:09:55 GMT

Hi Ksharp,

Thanks for your reply.

This solution works perfectly - I can see how you incorporated the counter and retain (something I didn't even think of trying!).

I am sure I will be able to use adaptations of this code for future problems.

Once again, this is much appreciated.

Pete

Re: Retain a flag for X number of months

PetePatel — Wed, 19 Jul 2017 16:13:53 GMT

Perfect, thank you!