## Difference between two dates greater then 28

Solved
Frequent Contributor
Posts: 147

# Difference between two dates greater then 28

I have dataset with three varaibles id startdate enddate. Dates are numerical sas formats.

I would like to pull out the records for id where the difference between the two succesive START dates is more then 28 days. There will be few missing dates too.

example:

ID    STARTDATE ENDDATE

001   19583      19604

001   19589      19609

001   19600      19610

001   19628      19638

001   19480      19520

002     .           .

002   19620      19624

002   19630      19634

Accepted Solutions
Solution
‎03-25-2014 09:35 AM
Frequent Contributor
Posts: 106

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

change if condition to

if id=lag(id) and startdate-lag(startdate)>=28;

All Replies
Frequent Contributor
Posts: 106

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

Hi Try this,

data test;

input ID \$3.   STARTDATE ENDDATE;

if startdate-lag(startdate)>=28;

cards;

001   19583      19604

001   19589      19609

001   19600      19610

001   19628      19638

001   19480      19520

002     .           .

002   19620      19624

002   19630      19634

;

run;

output:

001   19628      19638

->use sum function if you want dates after missing dates to be displayed

if sum(startdate,-lag(startdate))>=28;

Frequent Contributor
Posts: 147

## Re: Difference between two dates greater then 28

Thanks for your reply. It answered my query partially but I would like to calculate diff between dates for ID Specific. The logic you gave is working but it working across all the id's and it outputting even when the difference between two dates is greater then 28 for two different id.

Posts: 3,055

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

So, using the LAG function, you test to see if the ID of the current observation matches the ID of the previous observation, and also using the LAG function test to see if the dates are greater than 28.

--
Paige Miller
Solution
‎03-25-2014 09:35 AM
Frequent Contributor
Posts: 106

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

change if condition to

if id=lag(id) and startdate-lag(startdate)>=28;

Frequent Contributor
Posts: 147

## Re: Difference between two dates greater then 28

Thanks....its working.....is there any other option....we can use like(Group by ID as we do SQL and use lag function ) ......my query is more about finding the discrepancies of id's with dates greater then 28.....any query with group by id and using the lag function would be prefect fit for me......

Posts: 3,055

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv
```rakeshvvv wrote:

Thanks....its working.....is there any other option....we can use like(Group by ID as we do SQL and use lag function ) ......my query is more about finding the discrepancies of id's with dates greater then 28.....any query with group by id and using the lag function would be prefect fit for me......

```

I don't understand what you are asking for ... or how it is different than what we have already explained ... an example would definitely help. It also sounds like (although you don't specifically state this) that you are asking for a PROC SQL solution. Are you asking for a PROC SQL solution? If so, why does it have to be PROC SQL when a data step works perfectly well?

--
Paige Miller
Frequent Contributor
Posts: 147

## Re: Difference between two dates greater then 28

Posted in reply to PaigeMiller

Hi,

I have data set with  ID and with multiple VISITDATES......my query should output the records where difference between two successive visit dates is greater then 28 within a ID. hope it helps......

Posts: 3,055

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv
```
I have data set with  ID and with multiple VISITDATES......my query should output the records where difference between two successive visit dates is greater then 28 within a ID. hope it helps......
```

But that's what the code above has provided

--
Paige Miller
Super User
Posts: 23,771

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

This is similar to a 30 day readmission problem in medical studies. Search on here and you'll find SQL solutions to that effect.

It's a common request/issue.

Super User
Posts: 6,785

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

If you are saying that you only want one record per ID, just to identify the offending IDs, this would be a way.  It assumes your SAS data set is sorted by ID STARTDATE:

data want;

do until (last.id);

prior_start = startdate;

set have;

by id;

if first.id=0 and startdate - prior_start >= 28 then wanted='Y';

end;

if wanted='Y';

keep id;

run;

SQL would likely not be an option to accomplish this, since it doesn't guarantee the order of the incoming records.

Frequent Contributor
Posts: 106

## Re: Difference between two dates greater then 28

Posted in reply to rakeshvvv

how you want your output data to look like...

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 11 replies
• 813 views
• 0 likes
• 5 in conversation