topic Re: By group on 3 variables , unable to get output as required. in SAS Programming

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

HariGaggara — Thu, 14 Dec 2023 21:27:15 GMT

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.

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

Tom — Thu, 14 Dec 2023 22:04:30 GMT

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

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

HariGaggara — Thu, 14 Dec 2023 22:21:04 GMT

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.

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

HariGaggara — Mon, 18 Dec 2023 13:57:12 GMT

Thank you Tom. This solution works great.