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

[TheNovice]

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;

[TheNovice]

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;

[Patrick]

Please post the same question only once. 

I suggest that "we" answer only here.

[Kurt_Bremser]

---

@Patrick wrote:

Please post the same question only once. 

I suggest that "we" answer only here.

---

Merged them.

[Patrick]

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;

[TheNovice]

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

[PGStats]

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. 

[TheNovice]

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 .

[Kurt_Bremser · Accepted solution]

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.

[TheNovice]

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 .