topic Creating variable that indicates order by two other variables in SAS Programming

Creating variable that indicates order by two other variables

SarahW13 — Tue, 24 Dec 2019 11:44:44 GMT

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!

Re: Creating variable that indicates order by two other variables

PaigeMiller — Tue, 24 Dec 2019 11:52:40 GMT

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;

Re: Creating variable that indicates order by two other variables

Ksharp — Tue, 24 Dec 2019 12:19:05 GMT

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;