topic Re: concatenating all previous row in a new variable in SAS Programming

concatenating all previous row in a new variable

sasbee — Tue, 19 Jul 2022 18:51:56 GMT

I would like to create a method to check if a patient switch a medication (med_switch) or start a new medication that he/she had never had before (new_med) by comparing with the list of previous medication (any medication that the patient used before this current medication, list_of_previous_med).

subjid	year	prescription	med_switch	list_of_previous_med	new_med
a001	2001	med1	-	-	yes
a001	2002	med1	no	med1	no
a001	2003	med2	yes	med1	yes
a001	2003	med2	no	med1,med2	no
a002	2001	med1	-	-	yes
a002	2002	med2	yes	med1	yes
a002	2003	med1	yes	med1, med2	no
a002	2004	med3	yes	med1, med2	yes

I want to create the new variables in italic, however, I have no idea on how to do that on SAS.

I have been playing around with lag() and retain() function to compare rows of variables, however, I kept getting errors when I try to do it by subjid.

I hope the community can help.

Re: concatenating all previous row in a new variable

PaigeMiller — Tue, 19 Jul 2022 19:29:57 GMT

data want;
    set have;
    by subjid;
    length med_switch $ 3 commalist $ 256;
    retain commalist;
    prev_med=lag(prescription);
    if first.subjid then prev_med=' '; 
    if not first.subjid and prescription^=prev_med then  med_switch='yes';
    else med_switch='no';
    if first.subjid then commalist=' ';
    else commalist=cats(prev_med,',',commalist);
    if find(commalist,prescription,'it')>0 then new_med=0;
    else new_med=1;
    drop commalist prev_med;
run;

Re: concatenating all previous row in a new variable

sasbee — Tue, 19 Jul 2022 20:09:55 GMT

@PaigeMiller THANK YOU SO MUCH!!!!! This does exactly what I was hoping for. You put together the code so beautifully and succulently. I learned so much from the code.

Re: concatenating all previous row in a new variable

PaigeMiller — Tue, 19 Jul 2022 21:08:08 GMT

Thank you! Sometimes people use the word "succulently" to describe the results of my cooking; I have never had anyone describe my SAS code as being written "succulently", but I accept the compliment!