Relrec contains relationships between two or more records for two or more domains represented by idvar, idvarval, reltype and relid. For example, an adverse event is related to a concomitant medication (or a combo) which can be represented by a lab test (or more than one) for a subject. This can be represented by AESEQ, CMSEQ (CMGRPID for combo) and LBSEQ ( LGGRPID for multiple) for subject level data. LINKID and LINKGRP can be used if there is relation across all subjects. For example a Clinical event (CE) as an effect of investigational drug occurring surely for all subjects or connecting tumor results(TR) to tumor identification (TU). This macro pools all records from their respective domains based on the idvars, idvarvals, relid and reltype provided in the RELREC dataset. This can be used by the sponsor or reviewer to check for non anticipated relationships. Eg. possibility of a drug drug interaction.

libname xptfile xport ‘/folders/myfolders/tabulations/relrec.xpt’ access=readonly;

proc copy inlib=xptfile outlib=work;

run;

proc sql noprint;
select distinct rdomain into: domainc separated by ” ” from relrec;
quit;

%macro calldata;

%do l=1 %to %sysfunc(countw(&domainc)) ;
libname xptfile xport “/folders/myfolders/tabulations/%scan(&domainc,&l).xpt” access=readonly;
proc copy inlib=xptfile outlib=work;

run;
%end;
%mend;
%calldata;
%macro rele();
proc sort data=relrec out=rel2 ;
by relid ;
*where usubjid=”01-701-1146″;
run;

data rel2 ;
retain cnt;
set rel2;
by relid;
if first.relid then cnt+1;
run;
proc sql noprint;
select distinct cnt into :rels separated by ” ”
from rel2;
quit;
%do i=1 %to %sysfunc(countw(&rels));
data rel_&i;
set rel2;
where cnt=%scan(&rels,&i);
run;

proc sql noprint;
select distinct usubjid into :sub from rel_&i;
quit;

proc sql noprint;
select distinct rdomain,idvar,idvarval,left(trim(idvar))||left(trim(put(idvarval,$200.)))
as newvar
into:rdm separated by ” ” ,:idvar separated by ” “,:idvarval separated by ” “, :newvar separated by ” “
from rel_&i;
quit;
%put &sub check &rdm &idvar &idvarval &newvar;

%do j=1 %to %sysfunc(countw(&rdm));
data %scan(&rdm,&j)1;; set %scan(&rdm,&j);; where usubjid=”&sub” ; run; data %scan(&rdm,&j)%scan(&newvar,&j);
set %scan(&rdm,&j)_1;
where %scan(&idvar,&j)=%scan(&idvarval,&j);
/%put check %scan(&idvar,&j) %scan(&idvarval,&j) %scan(,&j);/
run;
%end;

data mergef;
set %do k=1 %to %sysfunc(countw(&rdm));
%scan(&rdm,&k)_%scan(&newvar,&k)
%end;
;
by usubjid;
run;
Proc print data=mergef;
by usubjid;
run;

%end;
proc datasets library=work kill;
run;
quit;
%mend;
%rele;

Outputs

For an ADAM data set it is necessary to use data in SDTM domain along with its respective SUPPQUAL domain. The programmer needs to follow necessary steps to transpose, rename and transform the IDVARs mentioned in the SUPPQUAL. This is a repetitive task and based on the power bestowed by CDISC through the SDTM metadata this process can be converted to a generic and reusable macro. I have tried to do the same here. This macro merges the SUPPQUAL domain with the SDTM domain for each different idvars and idvarvals combinations present in the SUPPQUAL

/Macro to merge SDTM data set with respective SUPP dataset for all distinct IDVAR in SUPP dataset INPUTS = DS1 is the SDTM dataset DS2 is the SUPP dataset ASSUMPTIONS: Both datasets should be in work library Look For a dataset with fin suffix as the final output WARNING Not to be run where IDVAR is blank example DM SDTM dataset/This enhancement can be added within the macro

%macro mergesupp(ds1=,ds2=);
/Creates a macro variable with distinct IDVARs from the SUPP dataset in the macro variable/
proc sql ;
select distinct idvar into :idvar separated by ” “
from &ds2;
quit;
/iterations for the number of words in the macro i.e. number of distinct IDVARs in the SUPP dataset/
%do i=1 %to %sysfunc(countw(&idvar));
%let seq=%scan(&idvar,&i);
proc sort data=&ds2 out=&ds2.2 ;
by usubjid idvar idvarval;
where idvar =”&seq”;
run;
data &ds2.2&i(rename =(idvarval2=&seq));
set &ds2.2;
idvarval2= input(idvarval,best.);
drop idvarval ;
run;
proc sort data=&ds2.2&i;
by usubjid &seq;
run;
/* Transposing for each IDVAR and creates a separate dataset for each distinct idvar*/
proc transpose data=&ds2.2&i out=&ds2.tr&i(drop =name label) ;
by usubjid &seq;
var qval;
id qnam;
idlabel qlabel;
run;

proc sort data=&ds1;
by usubjid &seq;
run;
proc sort data=&ds1 out=&ds1.&i;
by usubjid &seq;
run;

proc sort data=&ds2.tr&i;
by usubjid &seq;
run;
%end;
/* for first iteration the a dataset is created that is a product of merging of first
IDVAR transposed dataset and SDTM dataset*/
%do i=1 %to %sysfunc(countw(&idvar));
%let seq=%scan(&idvar,&i);

%if &i=1 %then %do;
data &ds1.1;
merge &ds1 &ds2.tr&i;
by usubjid &seq;
run;
%end;
/* for the second and greater iterations datasets are created for each iterations based on
number of distinct idvars greater than 1*/
%else %if &i ne 1 %then %do;
data &ds1.&i;
merge &ds1.%eval(&i-1) &ds2.tr&i;
by usubjid &seq;
run;

%end;
%end;
%let boom=%sysfunc(countw(&idvar));
/* final data set is with suffix fin*/
%if &boom = 1 %then %do;
data &ds1.2fin;
set &ds1.1;
run;
%end;
%else %if &boom ne 1 %then %do;
data &ds1.&boom.fin;
set &ds1.&boom;
run;
%end;
run;
%mend;
%mergesupp(ds1=lb,ds2=supplb);

I have been a SAS programmer in clinical research with 4+ years of experience having worked on TLGs, SDTM and ADAM. I currently guide people who are stuck with CDISC standards. I am am exploring advances, changes and development in CDISC standards. I am developing generic macros to aid the programmer in creating required outputs. There can be things that I missed or misunderstood while developing these programs. I would be highly grateful if you can provide inputs in form of suggestions, enhancements or guidance to me for my programs on shrey.virmani@gmail.com. I am currently looking for a full time opportunity as a SAS programmer in clinical research.