Search code examples
sas

Move records before all the others of the same group based on an Index


Suppose to have the following table:

data DB;
  input ID :$20. Date_Admission :date9. Date_Discharge :date9. Event :$20.;
  format Date_Admission :date9. Date_Discharge :date9.;
cards;
0001  18OCT2016 18NOV2016   0
0001  17FEB2018 28FEB2018   1
0004  15APR2016 18APR2016   0
0004  01JAN2017 15FEB2017   1
0004  15APR2018 18APR2018   0
....
; 

Is there a way to sort by ID but putting before all dates the row where The Event is 1 to get:

data DB1;
  input ID :$20. Date_Admission :date9. Date_Discharge :date9. Event :$20.;
  format Date_Admission :date9. Date_Discharge :date9.;
cards;
0001  17FEB2018 28FEB2018   1
0001  18OCT2016 18NOV2016   0
0004  01JAN2017 15FEB2017   1
0004  15APR2016 18APR2016   0
0004  15APR2018 18APR2018   0
....
;

While maintaining the other dates in increasing order.


Solution

  • Sort by ascending id, descending event, and ascending date_admission:

    proc sort data=db;
        by id descending event date_admission;
    run;
    
    ID  Date_Admission  Date_Discharge  Event
    0001    17FEB2018   28FEB2018       1
    0001    18OCT2016   18NOV2016       0
    0004    01JAN2017   15FEB2017       1
    0004    15APR2016   18APR2016       0
    0004    15APR2018   18APR2018       0