topic Transpose consecutive pairs of rows by group in SAS Programming

Transpose consecutive pairs of rows by group

medsocgrad — Fri, 18 Feb 2022 18:21:51 GMT

After years of reading the forum, I'm finally making my first post for something that is stumping me and there must be a solution. I have the dataset below:

data have;
  input id:32. event:$10. date:DATE9. reason:$8.;
  format date DATE9.;
  label id="id" event="event" date="date" reason="reason";
datalines;
1 entered 01JAN2020 .
1 discharged 01FEB2020 finished
2 entered 05MAR2021 .
2 discharged 15JUL2021 left
2 entered 20SEP2021 .
3 entered 05FEB2020 .
3 discharged 05MAR2020 finished
3 entered 11APR2021 .
3 discharged 01SEP2021 finished
4 entered 01DEC2018 .
4 discharged 01FEB2019 finished
4 entered 05MAR2019 .
4 discharged 06DEC2020 left
4 entered 03APR2021 .
;
run;

I would like to transpose this dataset to create the following:

Essentially the solution will need to look within each ID, find each pair of consecutive dates, and then transpose the data. If an id has an odd number of records, then there would be a "last" row for that id in the new dataset that is missing for non-present pair in the original data (see ids 2 and 4 in the image above). Using SAS 9.4 here.

Any help is greatly appreciated.

Re: Transpose consecutive pairs of rows by group

ballardw — Fri, 18 Feb 2022 18:53:11 GMT

Have you checked to see if you have any "discharged" that do not have a matching "entered"? Or a discharge prior to entered?

For your example data:

data want;
   set have;
   by id date;
   retain date1 date2 event_date1 event_date2 ;
   length event_date1 event_date2 $ 10 ;
   if event='entered' then do;
      /* clear out retained*/
      call missing(date1, date2, event_date1, event_date2);
      date1=date;
      event_date1=event;
      if last.id then output;
   end;
   if event='discharged' then do;
      date2=date;
      event_date2=event;
      output;
   end;
   keep id date1 date2 event_date1 event_date2 reason;
   format date1 date2 date9.;
run;

If you have a serious requirement for the order of the reason variable to be at the right look up any of the questions on this forum about such.

Re: Transpose consecutive pairs of rows by group

SASJedi — Fri, 18 Feb 2022 19:03:57 GMT

Here's an approach:

/* Just to be sure of order... */
proc sort data=have;
   by ID Date;
run;

data want;
   length id 8 Date1 Date2 8 Event1 Event2 $10 reason:$8.;
   set have;
   array d[2] Date1 - Date2;
   array e[2] Event1 - Event2;
   retain Date1 - Date2 Event1 - Event2;
   format Date1 - Date2 mmddyy10.;
   drop event date _:;
   by id;
   if first.id then do;
     call missing (of d[*], of e[*], _count);
   end;
   _count+1 ;
   d[_count]=date;
   e[_count]=event;
   if not mod(_count,2) then do;
      output;
      _count=0;
   end;
   else if last.id then do;
      call missing(date2, event2, reason);
      output;
   end;
run;

which produced this result:

id	Date1	Date2	Event1	Event2	reason
1	01/01/2020	02/01/2020	entered	discharged	finished
2	03/05/2021	07/15/2021	entered	discharged	left
2	09/20/2021	.	entered
3	02/05/2020	03/05/2020	entered	discharged	finished
3	04/11/2021	09/01/2021	entered	discharged	finished
4	12/01/2018	02/01/2019	entered	discharged	finished
4	03/05/2019	12/06/2020	entered	discharged	left
4	04/03/2021	.	entered

Re: Transpose consecutive pairs of rows by group

medsocgrad — Fri, 18 Feb 2022 22:34:32 GMT

Thank you for the quick response and elegant solution. This worked as hoped for!

Re: Transpose consecutive pairs of rows by group

Ksharp — Sat, 19 Feb 2022 10:39:47 GMT

data have;
  input id:32. event:$10. date:DATE9. reason:$8.;
  format date DATE9.;
  label id="id" event="event" date="date" reason="reason";
datalines;
1 entered 01JAN2020 .
1 discharged 01FEB2020 finished
2 entered 05MAR2021 .
2 discharged 15JUL2021 left
2 entered 20SEP2021 .
3 entered 05FEB2020 .
3 discharged 05MAR2020 finished
3 entered 11APR2021 .
3 discharged 01SEP2021 finished
4 entered 01DEC2018 .
4 discharged 01FEB2019 finished
4 entered 05MAR2019 .
4 discharged 06DEC2020 left
4 entered 03APR2021 .
;
run;
data want;
 merge have(where=(date1_event='entered') rename=(event=date1_event date=date_1 reason=reason_1))
       have(where=(date2_event='discharged') rename=(event=date2_event date=date_2 reason=reason_2));
 by id;
 output;
 call missing(of _all_);
 format date_1 date_2 mmddyy10.;
 drop reason_1;
 label date1_event=' ' date2_event=' ' date1_event=' ' date2_event=' ' date_1=' ' date_2=' ';
run;