BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PetePatel
Quartz | Level 8

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@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

6 REPLIES 6
art297
Opal | Level 21

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

PetePatel
Quartz | Level 8

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
art297
Opal | Level 21

@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

 

PetePatel
Quartz | Level 8

Perfect, thank you!

Ksharp
Super User
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;
PetePatel
Quartz | Level 8

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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