topic Re: Update the followup date in next column in SAS Programming

Update the followup date in next column

Sathish_jammy — Mon, 10 Jun 2019 08:57:36 GMT

Hi experts,

I need a clarification in the following sample data set. I need to update the followup dates in separate column.

data aaa;
input ID date mmddyy10.;
format date mmddyy10.;
cards;
10003 09/26/2003
10003 05/15/2004
10003 01/09/2007
10003 07/05/2007
;

Expected Output

+---------+-----------------+---------------+
| id | date | follow_up |
+---------+-----------------+---------------+
| 10003 | 2003-09-26 | 2004-05-15 |
| 10003 | 2004-05-15 | 2007-01-09 |
| 10003 | 2007-01-09 | 2007-07-05 |
| 10003 | 2007-07-05 | . |
+---------+----------------+----------------+

Please suggest some idea to organize the followup date as given above.

Thanks in advance!

Re: Update the followup date in next column

ChrisNZ — Mon, 10 Jun 2019 09:14:27 GMT

This should work:

data BBB;

merge AAA

AAA(firstobs=2 keep=DATE rename=(DATE=FOLLOW_UP));

run;

Re: Update the followup date in next column

Sathish_jammy — Tue, 11 Jun 2019 04:22:32 GMT

Dear @ChrisNZ

Thank u so much for your response. It works well.

but I have a question in this datastep.

For exmpl :

data aaa;
input ID date mmddyy10.;
format date mmddyy10.;
cards;
10003 09/26/2003
10003 05/15/2004
10003 01/09/2007
10003 07/05/2007
11111 01/09/2007
11111 07/05/2007
;

ID date FOLLOW_UP

10003	09/26/2003	05/15/2004
10003	05/15/2004	01/09/2007
10003	01/09/2007	07/05/2007
10003	07/05/2007	01/09/2007
11111	01/09/2007	07/05/2007
11111	07/05/2007	.

On the 4th Obs followup should be null. Instead it get next value of different ID. Please let me know to solve it. Much Thanks!

Re: Update the followup date in next column

ChrisNZ — Tue, 11 Jun 2019 10:35:56 GMT

In this case you need to also keep ID in the second table (rename it too) and compare the values