topic Re: calculate difference between 2 dates in same group in SAS Programming

How to calculate days between 2 dates in separate rows and same group

TheNovice — Sat, 20 Jun 2020 00:04:09 GMT

Hi there,

MY data looks like this. It's ordered by ID and date

BAN	col_date
100023	05-May-20
100023	04-Jun-20
123455	05-May-20

I need an output like below where the date from the second row becomes a variable in the first row and so on. Grouping has to be by ID. if there is only one record for the ID then the next date would be blank. ID could have single or multiple rows

BAN	col_date	next_date
100023	05-May-20	04-Jun-20
100023	04-Jun-20	.
123455	05-May-20	.

I tried sorting the data by ID descending date and the using the dif function to get the difference between dates but it's not working. What am i doing wrong? direction is appreciated.

Data test;
set Non;
by ID ;
Difference = Dif(col_date);
run;

calculate difference between 2 dates in same group

TheNovice — Sat, 20 Jun 2020 00:14:16 GMT

Hi there,

MY data looks like this. It's ordered by ID and date

BAN	col_date
100023	05-May-20
100023	04-Jun-20
123455	05-May-20

BAN	col_date	next_date
100023	05-May-20	04-Jun-20
100023	04-Jun-20	.
123455	05-May-20	.

I tried sorting the data by ID descending date and the using the dif function to get the difference between dates but it's not working. What am i doing wrong? direction is appreciated.

Data test;
set Non;
by ID ;
Difference = Dif(col_date);
run;

Re: calculate difference between 2 dates in same group

Patrick — Sat, 20 Jun 2020 00:44:02 GMT

Please post the same question only once.

I suggest that "we" answer only here.

Re: How to calculate days between 2 dates in separate rows and same group

Patrick — Sat, 20 Jun 2020 00:50:45 GMT

Something like below should do.

proc sort data=have out=want;
  by id descending col_date;
run;

data want;
  set want;
  by id;
  format next_date date9.;
  next_date=lag(col_date);
  if first.id then call missing(next_date);
run;

Re: How to calculate days between 2 dates in separate rows and same group

PGStats — Sat, 20 Jun 2020 04:05:46 GMT

If you have a license to SAS/ETS, use proc expand:

proc expand data=have out=want;
by BAN;
convert col_date=next_date / transformout=(lead);
run;

Note that, as a bonus, the format associated with col_date will also be given to next_date by the procedure.

Re: How to calculate days between 2 dates in separate rows and same group

TheNovice — Sat, 20 Jun 2020 05:05:42 GMT

Thanks Patrick, never seen proc expand but it works...it gave me the output...

the datastep example you gave was faster but it doesn't have the desired output

I got the below instead.. i will try again tomorrow since the account is locked. maybe the sort is wrong? Thank you again

BAN col_date next_date
100023 05-May-20 .
100023 04-Jun-20 05-May-20
123455 05-May-20 .

Re: calculate difference between 2 dates in same group

Kurt_Bremser — Sat, 20 Jun 2020 07:10:09 GMT

@Patrick wrote:

Please post the same question only once.

I suggest that "we" answer only here.

Merged them.

Re: How to calculate days between 2 dates in separate rows and same group

Kurt_Bremser — Sat, 20 Jun 2020 07:13:56 GMT

Use a "look-ahead":

data want;
merge
  have
  have (firstobs=2 rename=(ban=_ban col_date=next_date))
;
if ban ne _ban then next_date = .;
drop _ban;
run;

Untested, posted from my tablet.

Re: How to calculate days between 2 dates in separate rows and same group

TheNovice — Sat, 20 Jun 2020 15:20:13 GMT

Thank you Kurt,

This works!! i got the result i wanted

BAN col_date next_date
104175 28-May-20 29-May-20
104175 29-May-20 05-Jun-20
104175 05-Jun-20 15-Jun-20
104175 15-Jun-20 .
146960 05-May-20 .

Re: How to calculate days between 2 dates in separate rows and same group

TheNovice — Sat, 20 Jun 2020 15:22:58 GMT

Thanks Patrick, it did work. I missed the descending sort at first. Appreciate it.