topic Re: How to count intervals between dates for a given group? in SAS Programming

How to count intervals between dates for a given group?

saurabhc — Wed, 13 Nov 2013 00:05:06 GMT

Hi,

I have data that look like this. The main group is "id" each of which has a set of dates that may or may not repeat.

id date

1 1/1/2001

1 1/2/2002

1 1/2/2003

2 1/1/2005

2 1/2/2005

2 1/3/2006

2 1/4/2006

for id = 2, note that 1/1/2005 and 1/2/2005 are one month apart, so are 1/3/2006 and 1/4/2006. How can I can SAS to tell me if the dates for a given id are a fixed interval apart from the previous date that is different?

Can the potential solution be tweaked to modify the desired interval and also raise a flag if that interval is observed (one month in this example.)

NOTE: Consider dates in the DD/MM/YYYY format for this example.

Thank you!

Re: How to count intervals between dates for a given group?

art297 — Wed, 13 Nov 2013 00:38:29 GMT

It would help if you provide what you want your output file to look like given your example data.

Re: How to count intervals between dates for a given group?

Jagadishkatam — Wed, 13 Nov 2013 00:56:33 GMT

Please try

data have;

input id date :ddmmyy10.;

format date date9.;

cards;

1 1/1/2001

1 1/2/2002

1 1/2/2003

2 1/1/2005

2 1/2/2005

2 1/3/2006

2 1/4/2006

;

proc sort data=have;

by id date;

run;

data want;

set have;

by id date;

retain new_date;

new_date=lag(date);

if nmiss(new_date, date)=0 then flag=new_date ne date;

format new_date date9.;

if first.id then do ;

new_date=.;

flag=.;

end;

run;

Thanks,

Jagadish

Re: How to count intervals between dates for a given group?

saurabhc — Wed, 13 Nov 2013 21:34:59 GMT

id date interval1month

1 1/1/2001 0

1 1/2/2002 0

1 1/2/2003 0

2 1/1/2005 0

2 1/2/2005 1

2 1/2/2005 0

2 1/3/2006 0

2 1/4/2006 1

Here, the third variable tells us if the interval between the date on that row and the immediately preceding date on the previous row was one month (1) or not (0), thus an indicator.

Alternatively, a more generic solution could be

id date period

1 1/1/2001 0

1 1/2/2002 365

1 1/2/2002 0

1 1/2/2003 365

1 1/2/2003 0

2 1/1/2005 695

2 1/1/2005 0

2 1/2/2005 30

2 1/2/2005 0

2 1/3/2006 395

2 1/4/2006 30

where a variable simply counts the interval between a given date and the previous date in the preceding row.

Thanks.

Re: How to count intervals between dates for a given group?

saurabhc — Wed, 13 Nov 2013 21:51:14 GMT

Hi Jagadish,

Thank you for the suggestion.

It works but only tells me when the date for a given id. It does not give me any control over what interval to choose. I want to know if the date changed and also if the date was for example, one week apart from the previous date. How do I raise a flag for that?

Can we make the table look like this?

id date period

1 1/1/2001 0

1 1/2/2002 395

1 1/2/2002 0

1 1/2/2003 365

1 1/2/2003 0

2 1/1/2005 695

2 1/1/2005 0

2 1/2/2005 30

2 1/2/2005 0

2 1/3/2006 395

2 1/4/2006 30

the third column is days of difference between a given date and the previous date.

Re: How to count intervals between dates for a given group?

art297 — Wed, 13 Nov 2013 22:24:09 GMT

Are you looking to obtain the three measures computed with the following code?:

data intervals;

set have;

by id;

retain last_date;

interval1month=0;

months=0;

days=0;

if not first.id then do;

months=intck('months',last_date,date);

if months eq 1 then interval1month=1;

days=date-last_date;

end;

last_date=date;

drop last_date;

run;

Re: How to count intervals between dates for a given group?

Jagadishkatam — Thu, 14 Nov 2013 01:01:21 GMT

Hi Saurabh,

I modified my code as per your requirement, please check

data want;

set have;

by id date;

retain new_date;

new_date=lag(date);

if nmiss(new_date, date)=0 then period=intck('day',new_date,date);

format new_date date9.;

run;

Thanks,

Jagadish

Re: How to count intervals between dates for a given group?

Mit — Thu, 14 Nov 2013 01:22:59 GMT

Hi sourabh

How about this?

data two;
set have;
     startdate=lag(date);
     if startdate=. then startdate=date;
     FORMAT startdate DATE9.;

run;

data want;
set two;
period=date-startdate;
drop startdate;
run;

Re: How to count intervals between dates for a given group?

saurabhc — Fri, 15 Nov 2013 20:49:04 GMT

Hi Jagadish,

I could use your code by changing the end to

if first.id then do ;

new_date=startdate;

flag=0;

end;

I could do with just knowing what the difference between each date and the date of the previous row was. I later created indicators for the desired difference (1month, 1week etc.)

Thanks!

Hi Mit,

Your code essentially gave the same result. Thanks.

Hi Arthur,

Your code will be super useful when the intervals are fixed. I am not sure how the intck function may be used when we might have a variable interval of choice.

Thanks!

Re: How to count intervals between dates for a given group?

Peter_C — Wed, 20 Nov 2013 22:18:43 GMT


Hi Arthur,
Your code will be super useful when the intervals are fixed. I am not sure how the intck function may be used when we might have a variable interval of choice.
Thanks!

make the "interval" a parameter/prompt/macroVar and ensure it is selected from a valid list of intervals (see the doc about intervals in the INTCK() function)

Re: How to count intervals between dates for a given group?

art297 — Wed, 20 Nov 2013 23:33:50 GMT

: you had asked how to compute three specific measures. As suggested, you could easily turn the code into a macro that would allow you to specify whatever intervals or categories of intervals you need.

Re: How to count intervals between dates for a given group?

saurabhc — Thu, 21 Nov 2013 16:10:21 GMT

Hi Arthur!

Thanks very much for the suggestions.

Best,

Saurabh