Retain a flag for X number of months

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Retain a flag for X number of months

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

 

IDMonthflagEDdesired
1Jan-12000
1Feb-12000
1Mar-12000
1Apr-12131
1May-12001
1Jun-12001
1Jul-12000
1Aug-12121
1Sep-12001
1Oct-12000
1Nov-12000
1Dec-12000
2Jul-15000
2Aug-15141
2Sep-15111
2Oct-15101
2Nov-15001
2Dec-15000
2Jan-16000
2Feb-16000
3Jan-161101
3Feb-16001
3Mar-16001
3Apr-16001

Accepted Solutions
Solution
‎07-19-2017 12:14 PM
PROC Star
Posts: 7,471

Re: Retain a flag for X number of months

Posted in reply to PetePatel

@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

 

View solution in original post


All Replies
PROC Star
Posts: 7,471

Re: Retain a flag for X number of months

Posted in reply to PetePatel

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

Occasional Contributor
Posts: 15

Re: Retain a flag for X number of months

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.

 

3Jan-161101
3Feb-16001
3Mar-16001
3Apr-16001
4Jan-12000
4Feb-12121
4Mar-12001
4Apr-12000
4May-12000
Solution
‎07-19-2017 12:14 PM
PROC Star
Posts: 7,471

Re: Retain a flag for X number of months

Posted in reply to PetePatel

@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

 

Occasional Contributor
Posts: 15

Re: Retain a flag for X number of months

Perfect, thank you!

Super User
Posts: 10,028

Re: Retain a flag for X number of months

Posted in reply to PetePatel
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;
Occasional Contributor
Posts: 15

Re: Retain a flag for X number of months

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 256 views
  • 3 likes
  • 3 in conversation