By group on 3 variables , unable to get output as required.

[HariGaggara]

I sample input data looks like below after sorting for a SAS data set.

Id         date                 status

9212    12/01/2021     LC

9212    03/01/2022    LC

9212    05/01/2022    LC

9212    06/01/2022    CC

6083    12/01/2023     LC

8228    12/012023       LC

8228    03/01/2022    LC

8228    06/01/2022    CC

7756    02/01/2023    FC

7756    06/01/2023    FC

7756    07 /01/2023   FC

Want to have out put as below. 12/01/9999 is a default date.

Id         status    In_date              out_date

9212         LC        12/01/2021      06/01/2022

9212        CC       06/01/2022     12/01/9999

6083         LC      12/01/2023       12/01/9999

8228          LC      12/012023      06/01/2022 

8228        CC       06/01/2022    12/01/9999

7756        FC       02/01/2023     12/01/9999

Please help to code in data SAS script. 

Thanks in advance for the help. 

[Tom · Accepted solution]

To get the date from the NEXT record is a little difficult.

One way is to read the dataset twice, once starting with the second record.

You can use the NOTSORTED option on the BY statement to process by ID and STATUS even when the values of STATUS are only grouped and not sorted.

If you are going to use a made up date at least set it to the end of the year.

data have;
  input Id date :yymmdd. status $;
  format date yymmdd10.;
cards;
9212 2021-12-01 LC
9212 2022-03-01 LC
9212 2022-05-01 LC
9212 2022-06-01 CC
6083 2023-12-01 LC
8228 2023-12-01 LC
8228 2022-03-01 LC
8228 2022-06-01 CC
7756 2023-02-01 FC
7756 2023-06-01 FC
7756 2023-07-01 FC
;

data want;
  set have;
  by id status notsorted;
  format in_date out_date yymmdd10.;
  retain in_date;
  set have(firstobs=2 keep=date rename=(date=out_date)) have(obs=1 drop=_all_);
  if first.status then in_date=date;
  if last.status;
  if last.id then out_date='31dec9999'd ;
  drop date;
run;

proc print;
run;

Result

[HariGaggara]

Thank you Tom for your help and quick response.

I want to have out_date of id 9212 as 2022-06-01 for first entry which is the in_date of second entry.

and out_date of second entry should be default date if there is no status.

Id 6083 should have out_date as default date as there is no next status.

Appreciate your help. 

[HariGaggara]

Thank you Tom. This solution works great.