I have a dataset that came in this form:
ID | Reaction | Treatment | Dose Level |
---|---|---|---|
1 | Reaction 1 | Drug A + Drug B | A mg |
1 | Reaction 1 | Drug A + Drug B | B mg |
2 | Reaction 1 | Drug A + Drug B | A mg |
2 | Reaction 1 | Drug A + Drug B | B mg |
2 | Reaction 2 | Drug A + Drug B | A mg |
2 | Reaction 2 | Drug A + Drug B | B mg |
2 | Reaction 3 | Drug A + Drug B | A mg |
2 | Reaction 3 | Drug A + Drug B | B mg |
For these patients that received two drugs, they received one line per drug dose per reaction. For example, Patient #2 had 3 reactions and received 2 drugs, so she has 6 (3x2) line items. I am trying to condense the dataset to look like this for output:
ID | Reaction | Treatment | Dose Level |
---|---|---|---|
1 | Reaction 1 | Drug A + Drug B | A mg / B mg |
2 | Reaction 1; Reaction 2; Reaction 3 | Drug A + Drug B | A mg / B mg |
I am sure I could use the RETAIN statement, but I am very unfamiliar with it, and I cannot get it work out. I wanted to try PROC TRANSPOSE, but that turned out to be a bigger mess than it was worth. Any help would be greatly appreciated!
It sounds like you need to collect all the reactions within a dose level within treatment for each ID.
If so, this will solve your problem:
data temp;
input ID $ @3 Reaction $char10. +1 Treatment $char15. +1 Dose $char4.;
cards;
1 Reaction 1 Drug A + Drug B A mg
1 Reaction 1 Drug A + Drug B B mg
2 Reaction 1 Drug A + Drug B A mg
2 Reaction 1 Drug A + Drug B B mg
2 Reaction 2 Drug A + Drug B A mg
2 Reaction 2 Drug A + Drug B B mg
2 Reaction 3 Drug A + Drug B A mg
2 Reaction 3 Drug A + Drug B B mg
;
proc sort;
by id reaction treatment dose;
run;
data temp1;
set temp;
by id reaction treatment;
retain doses; length Doses $ 20;
if first.treatment then doses='';
doses = catx(';', doses, dose);
if last.treatment then output;
drop dose;
run;
proc sort;
by id treatment doses reaction;
run;
data temp2;
set temp1;
by id treatment doses;
retain reactions; length reactions $ 80;
if first.doses then reactions='';
reactions = catx(';', reactions, reaction);
if last.doses then output;
drop reaction;
run;
The first sort + data step collects the treatment-dose that give rise to a particular reaction.
The second sort+data step collects for each treatment-doses regime all of the reactions.
Hope this helps.
It sounds like you need to collect all the reactions within a dose level within treatment for each ID.
If so, this will solve your problem:
data temp;
input ID $ @3 Reaction $char10. +1 Treatment $char15. +1 Dose $char4.;
cards;
1 Reaction 1 Drug A + Drug B A mg
1 Reaction 1 Drug A + Drug B B mg
2 Reaction 1 Drug A + Drug B A mg
2 Reaction 1 Drug A + Drug B B mg
2 Reaction 2 Drug A + Drug B A mg
2 Reaction 2 Drug A + Drug B B mg
2 Reaction 3 Drug A + Drug B A mg
2 Reaction 3 Drug A + Drug B B mg
;
proc sort;
by id reaction treatment dose;
run;
data temp1;
set temp;
by id reaction treatment;
retain doses; length Doses $ 20;
if first.treatment then doses='';
doses = catx(';', doses, dose);
if last.treatment then output;
drop dose;
run;
proc sort;
by id treatment doses reaction;
run;
data temp2;
set temp1;
by id treatment doses;
retain reactions; length reactions $ 80;
if first.doses then reactions='';
reactions = catx(';', reactions, reaction);
if last.doses then output;
drop reaction;
run;
The first sort + data step collects the treatment-dose that give rise to a particular reaction.
The second sort+data step collects for each treatment-doses regime all of the reactions.
Hope this helps.
One thing DLings proposed code doesn't cover is a duplicate entry, such as:
data have;
informat reaction $10.;
informat treatment $20.;
informat dose_level $5.;
input ID Reaction & Treatment & Dose_Level &;
cards;
1 Reaction 1 Drug A + Drug B A mg
1 Reaction 1 Drug A + Drug B B mg
2 Reaction 1 Drug A + Drug B A mg
2 Reaction 1 Drug A + Drug B A mg
2 Reaction 1 Drug A + Drug B B mg
2 Reaction 2 Drug A + Drug B A mg
2 Reaction 2 Drug A + Drug B B mg
2 Reaction 3 Drug A + Drug B A mg
2 Reaction 3 Drug A + Drug B B mg
;
proc sql noprint;
create table need1 as
select distinct *
from have
group by id, reaction, treatment, dose_level
having min(id) gt 0
;
create table need2 as
select distinct id,treatment,reaction
from need1
group by id, treatment
having min(id) gt 0
;
create table need3 as
select distinct id,treatment,dose_level
from need1
group by id, treatment
having min(id) gt 0
;
quit;
proc transpose data=need2 out=need4 (drop=_:) prefix=reaction;
var reaction;
by id treatment;
run;
proc transpose data=need3 out=need5 (drop=_:) prefix=dose;
var dose_level;
by id treatment;
run;
data want (drop=reaction1-reaction3 dose1-dose2);
set need4;
set need5;
reaction=catx(',',of reaction1-reaction3);
dose_level=catx('/',of dose1-dose2);
run;
I don't mean to imply that my approach is any better, but it does correct for that problem.
Art
Thanks Art. I was thinking about that after my quick reply. More context around how the data is actually structured is needed, but the main ideas are all here.
data temp; input ID $ @3 Reaction $char10. +1 Treatment $char15. +1 Dose $char4.; cards; 1 Reaction 1 Drug A + Drug B A mg 1 Reaction 1 Drug A + Drug B B mg 2 Reaction 1 Drug A + Drug B A mg 2 Reaction 1 Drug A + Drug B B mg 2 Reaction 2 Drug A + Drug B A mg 2 Reaction 2 Drug A + Drug B B mg 2 Reaction 3 Drug A + Drug B A mg 2 Reaction 3 Drug A + Drug B B mg ; proc sort; by id reaction treatment dose; run; data want; set temp; by id; retain _reaction _treatment _dose; length _reaction _treatment _dose $ 100 ; if first.id then call missing (of _:); if not find(_reaction,reaction) then _reaction=catx(';',_reaction,reaction); if not find(_treatment,treatment) then _treatment=catx(' ',_treatment,treatment); if not find(_dose,dose) then _dose=catx('/',_dose,dose); if last.id then output; drop reaction treatment dose ; run;
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: