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
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.
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.