DATA Step, Macro, Functions and more

Overlapping Dates- long to wide dataset

Reply
New Contributor
Posts: 4

Overlapping Dates- long to wide dataset

[ Edited ]

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? 

Super User
Posts: 9,611

Re: Overlapping Dates- long to wide dataset

Posted in reply to ntburton1

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

Re: Overlapping Dates- long to wide dataset

[ Edited ]
Posted in reply to KurtBremser

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! 

Super User
Posts: 22,874

Re: Overlapping Dates- long to wide dataset

Posted in reply to ntburton1

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 Smiley Wink

 

 

Super User
Posts: 9,611

Re: Overlapping Dates- long to wide dataset

Posted in reply to ntburton1

My algorithm is complete. Translate it to SAS code, and post that here, so we can make the necessary corrections.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 195

Re: Overlapping Dates- long to wide dataset

Posted in reply to ntburton1
Your data seems to be sorted by id and start_date - should be ok. Try to write the data step using the description provided by @KurtBremser. Post code and log if you receive an error message.
Super User
Posts: 10,626

Re: Overlapping Dates- long to wide dataset

Posted in reply to ntburton1
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;
Ask a Question
Discussion stats
  • 6 replies
  • 182 views
  • 4 likes
  • 5 in conversation