Patient drug sequence groups

[dxiao2017]

Hi @pink_poodle , would a simple proc sql step solve your question? The code and results are as follows.

data want;
	input patient time drug $ group $;
	datalines;
1 1 A AB
1 2 B AB
2 1 B BA
2 2 A BA
;
run;
proc sql;
select patient, time,drug,
       cat('patient',patient,
           ' belongs to group ',
            '"',compress(group),'"') as group
   from want;
quit;

data want;
	input patient time drug $ group $;
	datalines;
1 1 A ABB
1 2 B ABB
1 3 B ABB
2 1 B BA
2 2 A BA
;
run;
proc sql;
select patient, time,drug,
       cat('patient',patient,
           ' belongs to group ',
            '"',compress(group),'"') as group
   from want;
quit;

[dxiao2017]

Hi @pink_poodle , I did not understand your question correctly in my previous thread. Here is the correct steps: (1) use lag() function and cat() function to create the group column, (2) use proc sql to produce correspondence patient and group table, and (3) use sql join to produce final table which fill in the correspondence group for each patient. The code and result tables are as follows. 

My solution looks take much more steps than @Ksharp 's solution (that you pick as the solution for the question), but I think that solution consumes a lot difficult thinking and the logic behind the code is very difficult to follow. My solution looks take more steps, but the thinking behind it is very simple. What do you think? Please kindly let me know if my code answer your question, 😀thanks!

data have1;
	input patient time drug $;
	datalines;
1 1 A
1 2 B
2 1 B
2 2 A
;
run;
proc print data=have1;run;
data want1;
   set have1;
   by patient;
   drugprev1=lag1(drug);
   if first.patient then drugprev1=' ';
   if last.patient then group=cat(compress(drugprev1),
                                  compress(drug));
run;
proc print data=want1;run;
proc sql;
create table patientgrp as
select distinct patient,group
   from want1
   where group is not null;
select * from patientgrp;
quit;
proc sql;
create table wantfinal as
select w.patient,w.time,pg.group
   from want1 as w left join
        patientgrp as pg
   on w.patient=pg.patient;
select * from wantfinal;
quit;