Creating variable that indicates order by two other variables

[SarahW13]

Hello all,

I am trying to figure out how to create a sequence variable that is based on two other variables (med [a categorical variable] and date)

Below is what I have:

id           med            date

1            A                 01/01/2019

1            A                 02/01/2019

1            B                 04/01/2019

1            B                 05/01/2019

1            A                 06/01/2019

1            A                 07/01/2019

2            A                 03/01/2019

2            C                 04/01/2019

2            A                 06/01/2019

Below is what I want:

id           med            date                 Seq

1            A                 01/01/2019     1

1            A                 02/01/2019     1

1            B                 04/01/2019     2

1            B                 05/01/2019     2

1            A                 06/01/2019     3

1            A                 07/01/2019     3

2            A                 03/01/2019     1

2            C                 04/01/2019     2

2            A                 06/01/2019     3

Since id#1 received med A first, when that person switched to med B for a period of time, I want that period on med B to be seq #2. Even when that person switched back to med A again, since s/he already received med B, I want the new start of med A to be indicated with seq #3 and not seq#1.

Any advice?

Thanks in advance!

[PaigeMiller · Accepted solution]

proc sort data=have;
    by id date;
run;

data want;
    set have;
    by id;
    prev_med=lag(med);
    if first.id then count=1;
    if not first.id and med^=prev_med then count+1;
    drop prev_med;
run;

[Ksharp]

data have;
input id           med    $        date :	$10.;
cards;
1            A                 01/01/2019
1            A                 02/01/2019
1            B                 04/01/2019
1            B                 05/01/2019
1            A                 06/01/2019
1            A                 07/01/2019
2            A                 03/01/2019
2            C                 04/01/2019
2            A                 06/01/2019
;

data want;
 set have;
 by id med notsorted;
 if first.id then seq=0;
 seq+first.med;
run;