Deriving a record value from multiple source datasets

[smackerz1988]

Hello,

First time writing an ADDS Dataset and came across this algorithm for deriving TCWRESOT. Brought in SDTM DS and merged with ADSL by subject ID. However came across an issue I need help with (I've highlighted the particular problem)

"If a subject has a record in SDTM.DS where DSSCAT equals "END OF TREATMENT" and DSDECOD is not equal to "COMPLETED", create a record with PARAMCD set to "TCWRESOT". If DSDECOD equals "OTHER" then set AVALC to "Other " concatenated with DSTERM enclosed in parenthesis. Else, if DSDECOD equals "ADVERSE EVENT" then set AVALC to "Adverse event " concatenated with the relevant (record where AE.AEACN equals "DRUG WITHDRAWN") AE.AETERM and AE.AEREL formatted in sentence case, enclosed in parenthesis and separated with a semicolon, for example "Adverse event (Headache; possibly related)". Else, set AVALC to DSDECOD formatted in sentence case."

So just wondering what the most efficient approach to this is. My initial thoughts are to subset the AE dataset where AEACN equals DRUG WITHDRAWN and concatenate AETERM and AEREL. Then subset the SDTM DS dataset where DSDECOD equals 'ADVERSE EVENT' them merge by subject and then possibly use PROC SQL to insert those concatenated values into a macro to bring into the current data step to use for that conditional value of  TCWRESOT. If anyone has more experience or a more efficient approach I would greatly appreciate it.

[Reeza · Accepted solution]

I'd just use OUTPUT in a data step. Where the OUTPUT is placed does matter so test this carefully. 

Simplistic example similar to your requirements, if anyone age 14 should have a new record with age 15 as well. 

data class;
set sashelp.class;
output; *writes record to output data set;
if age = 14 then do;
*change age;
age=15;
output; *outputs record again;
end;
run;

title 'Original data set';
proc print data=sashelp.class;
run;

title 'New Data set';
proc print data=class;
run;