topic Comparing start dates and end dates between different rows in SAS Programming

Comparing start dates and end dates between different rows

jerrylshen — Mon, 24 Jun 2019 18:15:39 GMT

Example dataset

╔═════════╦═══════╦════════════╦══════════╗
║ subject ║ event ║ start_date ║ end_date ║
╠═════════╬═══════╬════════════╬══════════╣
║ s1 ║ e1 ║ 2-Mar-19 ║ 1-Jun-19 ║
║ s2 ║ e1 ║ 1-Mar-19 ║ 1-May-19 ║
╚═════════╩═══════╩════════════╩══════════╝

Expected Output:

╔═════════╦═══════╦════════════╦══════════╦════════════╗
║ subject ║ event ║ start_date ║ end_date ║ query_text ║
╠═════════╬═══════╬════════════╬══════════╬════════════╣
║ s1 ║ e1 ║ 2-Mar-19 ║ 1-Jun-19 ║ error? ║
║ s2 ║ e1 ║ 1-Mar-19 ║ 1-May-19 ║ error? ║
╚═════════╩═══════╩════════════╩══════════╩════════════╝

I want to return all that have the same 'event' and where the 'end_date' of one with the earlier 'start_date' is greater than (>) the 'start_date' of the other one with the later start_date.

Here, subject 's2' has an earlier 'start_date' than 's1' but 's2' has a later 'end_date' than "s1's" 'start_date', so these two would get returned

This is what I have so far, but I'm not sure how to make sure what I'm comparing have the same 'event':

data Out_2;
/* set relevant dataset */
if input(compress(end_date,"/"), ??date9.) > input(compress(start_date,"/"), ??date9.);

/* do something */

run;

Thanks

Re: Comparing start dates and end dates between two rows

hashman — Mon, 24 Jun 2019 16:01:29 GMT

@jerrylshen:

It's nearly impossible to understand from your description what you need to achieve. Please provide a sample of output that would give an idea of its relationship with the sample input. Preferably, provide both with more than one sample "event". Also, since your code includes compressing forward slashes, include some of them in your input; otherwise it's not clear why you're doing this (in particular, with the sample input you've shown it's not needed).

Kind regards

Paul D.

Re: Comparing start dates and end dates between two rows

ballardw — Mon, 24 Jun 2019 16:24:47 GMT

It might help to provide some data from your set ALL and what the actual expected result would be of this process as data steps.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Comparing start dates and end dates between two rows

jerrylshen — Mon, 24 Jun 2019 16:56:43 GMT

oops, sorry, I forgot to include the expected output. the post has been edited, thanks

Re: Comparing start dates and end dates between two rows

jerrylshen — Mon, 24 Jun 2019 16:57:52 GMT

oops, sorry, I have now included the expected the output. I also edited the set all thing, I just want the assumption of the dataset to be like in the example