DATA Step, Macro, Functions and more

How to count intervals between dates for a given group?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

How to count intervals between dates for a given group?

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/1/2001

1 1/2/2002

1 1/2/2002

1 1/2/2003

1 1/2/2003

2 1/1/2005

2 1/1/2005

2 1/1/2005

2 1/2/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!


Accepted Solutions
Solution
‎11-12-2013 07:56 PM
Trusted Advisor
Posts: 1,131

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

Please try

data have;

     input id date :ddmmyy10.;

     format date date9.;

cards;

1 1/1/2001

1 1/1/2001

1 1/2/2002

1 1/2/2002

1 1/2/2003

1 1/2/2003

2 1/1/2005

2 1/1/2005

2 1/1/2005

2 1/2/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

Thanks,
Jag

View solution in original post


All Replies
PROC Star
Posts: 7,363

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

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

Occasional Contributor
Posts: 19

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

id date          interval1month

1 1/1/2001     0

1 1/1/2001     0

1 1/2/2002     0

1 1/2/2002     0

1 1/2/2003     0

1 1/2/2003     0

2 1/1/2005     0

2 1/1/2005     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/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/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.

Solution
‎11-12-2013 07:56 PM
Trusted Advisor
Posts: 1,131

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

Please try

data have;

     input id date :ddmmyy10.;

     format date date9.;

cards;

1 1/1/2001

1 1/1/2001

1 1/2/2002

1 1/2/2002

1 1/2/2003

1 1/2/2003

2 1/1/2005

2 1/1/2005

2 1/1/2005

2 1/2/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

Thanks,
Jag
Occasional Contributor
Posts: 19

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

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/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/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.

Trusted Advisor
Posts: 1,131

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

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

Thanks,
Jag
PROC Star
Posts: 7,363

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

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;

Frequent Contributor
Frequent Contributor
Posts: 83

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

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;

Occasional Contributor
Posts: 19

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

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!

Valued Guide
Posts: 2,175

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

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)

PROC Star
Posts: 7,363

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

: 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.

Occasional Contributor
Posts: 19

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

Hi Arthur!

Thanks very much for the suggestions.

Best,

Saurabh

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 601 views
  • 12 likes
  • 5 in conversation