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 |
@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
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
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 |
@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
Perfect, thank you!
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.