topic Re: Patient drug sequence groups in SAS Programming

Patient drug sequence groups

pink_poodle — Tue, 06 Feb 2024 23:52:45 GMT

I have a table with three variables: patient, time, drug. Let us say there are two patients. First patient received drug A at time 1 followed by drug B at time 2. Second patient received drug B at time 1 followed by drug A at time 2. I want to accumulate a substring for the group variable, such that patient 1 belongs to group "AB" and patient 2 belong to group "BA". Any suggestions are welcome!

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;

Re: Patient drug sequence groups

rvikram — Wed, 07 Feb 2024 02:24:37 GMT

What is your question?

Re: Patient drug sequence groups

rvikram — Wed, 07 Feb 2024 02:26:02 GMT

If I were you, I’d check the syntax for data lines again.

You need infile statement, and preferably a DLM keyword. Please check again.

Re: Patient drug sequence groups

pink_poodle — Wed, 07 Feb 2024 02:26:07 GMT

My question is how to accumulate a substring for the group variable, such that patient 1 belongs to group "AB" and patient 2 belong to group "BA" ?

Re: Patient drug sequence groups

rvikram — Wed, 07 Feb 2024 02:32:54 GMT

If you are looking to group certain variables, you can write a proc sql step with group by.

If you don’t want duplicate values, you can write a proc sort step with nodup, or select distinct within proc sql.

Re: Patient drug sequence groups

Ksharp — Wed, 07 Feb 2024 02:37:22 GMT

/*Assuming there are no duplicated obs in your data*/
data have;
	input patient time drug $;
	datalines;
		1 1 A 
		1 2 B 
		2 1 B 
		2 2 A 
	;
run;

data want;
do until(last.patient);
 set have;
 by patient;
 length group $ 10;
 group=cats(group,drug);
end;
do until(last.patient);
 set have;
 by patient;
 output;
end;
run;

Re: Patient drug sequence groups

pink_poodle — Wed, 07 Feb 2024 02:46:33 GMT

@rvikram, it should work, please give it a try :).

Re: Patient drug sequence groups

pink_poodle — Wed, 07 Feb 2024 02:58:52 GMT

@Ksharp , thank you, this is very helpful! I would like to have "ABB" group for duplicates, could you please advice?:

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;

Re: Patient drug sequence groups

mkeintz — Wed, 07 Feb 2024 02:58:30 GMT

Pass through each ID twice, once to build GROUP, once to output it:

data have;
  input patient time drug $;
  datalines;
1 1 A 
1 2 B 
2 1 B 
2 2 A 
run;

data want;
  set have (in=firstpass)
      have (in=secondpass);
  by id;
  retain group '          ' ;  /*As many blanks as needed for group */
  if first.id then group=drug;
  else if firstpass then group=cats(group,drug);
  if secondpass;
run;

Re: Patient drug sequence groups

Ksharp — Wed, 07 Feb 2024 03:01:23 GMT

Just try my original code. No need any change.

Re: Patient drug sequence groups

ArdigenSupport — Fri, 25 Jul 2025 20:48:39 GMT

Sure! You can achieve this easily using a PROC SQL approach or DATA step in SAS by grouping by patient and then accumulating the drug sequence based on time. Here's one clean solution using PROC SQL that works well for your scenario:
proc sql;
create table want as
select patient,
catx('',
/* sort and concatenate the drugs by time for each patient */
max(case when time=1 then drug end),
max(case when time=2 then drug end)
) as group
from your_table
group by patient;
quit;

Re: Patient drug sequence groups

ArdigenSupport — Fri, 25 Jul 2025 20:55:02 GMT

You're looking to create a new variable, group, that reflects the drug sequence for each patient based on the time variable. To do this, we need to sort the data by patient and time, then concatenate the drug values for each patient in the correct order.

Here’s a solution that achieves what you want using a DATA step:

data want;
input patient time drug $ group $;
;
1 1 A AB
1 2 B AB
2 1 B BA
2 2 A BA
;
run;

/* Step 1: Sort the data by patient and time */
proc sort data=want;
by patient time;
run;

/* Step 2: Concatenate the drug values per patient */
data want_grouped;
set want;
by patient;

retain group_string;

/* Accumulate drug values for each patient */
if first.patient then group_string = ''; /* Reset at start of new patient */
group_string = ('', group_string, drug); /* Append drug to the group string */

/* Output once all records for a patient are accumulated */
if last.patient then do;
group = group_string;
output;
end;

drop group_string;
run;

proc print data=want_grouped;
run;

Re: Patient drug sequence groups

dxiao2017 — Thu, 31 Jul 2025 09:48:44 GMT

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;

Re: Patient drug sequence groups

dxiao2017 — Tue, 05 Aug 2025 13:19:02 GMT

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;

Re: Patient drug sequence groups

dxiao2017 — Fri, 08 Aug 2025 12:52:34 GMT

Hi @pink_poodle , after tried many times using if first. and retain etc. techniques and failed, I suddenly realized that this is a very simple problem about proc transpose, the code is as simple as follows :

data have;
	input patient time drug $;
	datalines;
1 1 A
1 2 B
1 3 B
2 1 B
2 2 A
;
run;
proc print data=have;run;
proc transpose data=have out=want;
   by patient;
   id time;
   var drug;
run;
proc print data=want;run;

And then you can use cat() function to put column 1,2,3 together to produce the patient group column of this typical cross over design. The conclusion is: proc transpose is the simple and efficient solution.

Re: Patient drug sequence groups

dxiao2017 — Fri, 08 Aug 2025 13:26:17 GMT

Hi @pink_poodle , the complete steps are as follows (using proc transpose to produce the group column):

data have;
	input patient time drug $;
	datalines;
1 1 A
1 2 B
1 3 B
2 1 B
2 2 A
;
run;
proc print data=have;run;
data have1;
   set have;
   timechar=cat('t',put(time,1.));
run;
proc print data=have1;run;
proc transpose data=have1 out=want;
   by patient;
   id timechar;
   var drug;
run;
proc print data=want;run;
data want1;
   set want;
   group=compress(cat(t1,t2,t3));
run;
proc print data=want1;run;
proc sql;
select h.*,
       w1.group
   from have as h left join
        want1 w1
   on h.patient=w1.patient;
quit;

Re: Patient drug sequence groups

dxiao2017 — Fri, 08 Aug 2025 14:02:34 GMT

The last table somehow changed the order of the time column. So order by should be used here:

proc sql;
select h.*,
       w1.group
   from have as h left join
        want1 w1
   on h.patient=w1.patient
   order by h.patient,h.time;
quit;