RELREC Pooling

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