Populating the sequence

[1239]

Hi,

Please find the below sample dataset. I want for each Test whenever Visit = 1 then Visit_order to be as

   if Visit 1 then Visit_order =  1

   if Visit 14 then Visit_order = 2

   if Visit 28 then Visit_order = 3

and

   if Visit 2 then Visit_order =  4

   if Visit 14 then Visit_order = 5

   if Visit 28 then Visit_order = 6.

Could any please help me in achieving this? I have attached expected output for reference.

data test;

input Test $1 Visit 8.;

datalines;

A 1

A 14

A 28

B 2

B 14

B 28

C 2

C 14

C 28

D 1

D 14

D 28

run;

[Ksharp]

Your logic is ambiguous. Why the same visit 14 28 get different visit_order?

data test;
input Test $1 Visit 8.;
datalines;
A 1
A 14
A 28
B 2
B 14
B 28
C 2
C 14
C 28
D 1
D 14
D 28
;
run;
data test;
 set test;
 retain _visit;
 if test ne lag(test) then _visit=visit;
run;
data want(drop=_visit);
 set test;
 select;
  when(_visit=1 and visit=1 ) visit_order=1;
  when(_visit=1 and visit=14) visit_order=2; 
  when(_visit=1 and visit=28) visit_order=3;
  when(_visit=2 and visit=2) visit_order=4;
  when(_visit=2 and visit=14) visit_order=5;
  when(_visit=2 and visit=28) visit_order=6;
  otherwise;
 end;
run;

Ksharp