I have data set that is set up as follows
ID | Start_Date | End_Date |
1 | 25-Jan-17 | 25-Mar-17 |
1 | 1-Feb-17 | 14-Feb-17 |
2 | 1-Apr-17 | 30-Apr-17 |
2 | 15-Apr-17 | 20-Apr-17 |
2 | 1-Nov-17 | 25-Nov-17 |
3 | 16-Jun-17 | 20-Jun-17 |
4 | 1-May-17 | 20-May-17 |
4 | 21-May-17 | 30-May-17 |
I need to go from this, to one line of data for each ID that indicates/flags whether or not the ID had overlapping dates. For example:
ID | overlap_flag |
1 | 1 |
2 | 1 |
3 | 0 |
4 | 0 |
I'm not sure how to do this in SAS, although I'm sure it's quite easy. Any suggestions?
In a data step with by id:
- retain the flag variable
- retrieve the previous end_date with the lag() function.
- at first.id, set the flag to zero;
- if not first.id, compare the lagged end_date with the current start_date, and set flag to 1 if appropriate
- at last.id, output
- keep id and flag
hmmm I'm not sure I understand this. I'm a pretty novice SAS user and have never used the lag function.
I'd have to sort on the start date first, no? Or does the lag function do this?
Many thanks for your assistance!
Yes you do need to sort, but your data was presented as sorted so in general we'll assume it's as shown.
If you run into issues feel free to post your code and explain the issues.
We're more than happy to assist, less happy to do your work 😉
My algorithm is complete. Translate it to SAS code, and post that here, so we can make the necessary corrections.
data have;
infile cards expandtabs truncover;
input ID Start_Date : date11. End_Date : date11.;
format Start_Date End_Date : date11.;
cards;
1 25-Jan-17 25-Mar-17
1 1-Feb-17 14-Feb-17
2 1-Apr-17 30-Apr-17
2 15-Apr-17 20-Apr-17
2 1-Nov-17 25-Nov-17
3 16-Jun-17 20-Jun-17
4 1-May-17 20-May-17
4 21-May-17 30-May-17
;
run;
data temp;
set have;
by id;
flag=(Start_Date<lag(End_Date));
if first.id then flag=0;
run;
proc sql;
select id,max(flag) as flag
from temp
group by id;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.