topic Retaining Lines and Combining into a New Variable in ODS and Base Reporting

Retaining Lines and Combining into a New Variable

djbateman — Tue, 09 Aug 2011 18:04:49 GMT

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!

Retaining Lines and Combining into a New Variable

DLing — Tue, 09 Aug 2011 18:43:13 GMT

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.

Retaining Lines and Combining into a New Variable

art297 — Tue, 09 Aug 2011 19:14:40 GMT

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 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

Retaining Lines and Combining into a New Variable

DLing — Tue, 09 Aug 2011 19:38:25 GMT

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.

Retaining Lines and Combining into a New Variable

Ksharp — Thu, 18 Aug 2011 05:54:43 GMT

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