BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saurabhc
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

11 REPLIES 11
art297
Opal | Level 21

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

saurabhc
Fluorite | Level 6

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.

Jagadishkatam
Amethyst | Level 16

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
saurabhc
Fluorite | Level 6

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.

Jagadishkatam
Amethyst | Level 16

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
art297
Opal | Level 21

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;

Mit
Calcite | Level 5 Mit
Calcite | Level 5

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;

saurabhc
Fluorite | Level 6

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!

Peter_C
Rhodochrosite | Level 12

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)

art297
Opal | Level 21

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

saurabhc
Fluorite | Level 6

Hi Arthur!

Thanks very much for the suggestions.

Best,

Saurabh

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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