Mapping to SDTM

This macro requires metadata in form of excel sheet. example

And SDTM meta data example

The program performs three types of mapping. Rename, straight and transpose. Please have a look at the input and output LB datset

%macro mapper(ds=);
proc datasets lib=work
nolist kill;
quit;
run;
proc import datafile=”/folders/myfolders/SDTM_META”
out=&ds._meta DBMS=xlsx replace;
sheet=”&ds”;
getnames=yes;
run;
proc import datafile=”/folders/myfolders/MAPPING”
out=&ds._map DBMS=xlsx replace;
sheet=”&ds._LEG”;
getnames=yes;
run;

data one;
set &ds._map;
where upcase(DERIVATION)=”ONE”
and upcase(left(trim(SDTM))) ne upcase(left(trim(OLD)));
run;

proc contents data=&ds out=&ds._CONT;
run;

proc sql noprint;
select left(trim(a.old)) ||”=”|| left(trim(a.SDTM))
into :rename separated by ” “
from one as a
where a.old in (select distinct name from &ds._cont) ;
quit;

%put check &rename;
data &ds._new;
set &ds;
rename &rename;
run;
data &ds._many;
set &ds._map;
where upcase(DERIVATION)=”MANY”;
run;
%let dsid=%sysfunc(open(&ds._many));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

%put nobs= &nobs;

%if &nobs ne 0 %then %do;

proc sql noprint;
select count(distinct SDTM) into :transcn from &ds._MANY;
quit;
run;

proc sql noprint;
select count(distinct SDTM) into :transcn from &ds._MANY;
quit;
proc sort data=&ds._many;
by SDTM;
run;
data &ds._many;
retain id;
set &ds._many;
by SDTM;
if first.sdtm then ID+1;
run;

%do i=1 %to &transcn;
data tran_where_&ds.;
set &ds.many; where id=&i; call symputx(“byvar”,key); call symputx(“sdtm”,sdtm); run; proc sql noprint; select distinct old into:vartotr separated by ” ” from tran_where&ds.
where id=&i;
quit;
proc transpose data=&ds out=trans_&ds.&i(rename=(col1=&sdtm)) ; by &byvar; var &vartotr; run; proc sql noprint; select distinct id_var into :ID_VARs from tran_where&ds;
quit;
%if &id_VARS ne %then %do;
data trans_&ds.&i(rename=( _name=&id_vars));
set trans_&ds.&i; run; %end; %else %if &id_vars eq %nrstr() %then %do; data trans&ds.&i(drop=_name);
set trans_&ds.&i; run; %end; %end; options mprint; proc sql noprint; select distinct memname into :mergevars separated by ” ” from dictionary.columns where memname contains (“TRANS&ds.“); quit; proc sql noprint; select distinct key_SDTM into: byvar2 separated by ” ” from &ds._MAP ; quit; %do j=1 %to &transcn; data trans&ds.&j (rename=(%do k=1 %to &transcn; %scan(&byvar,&k)=%scan(&byvar2,&k)%end ;)); set trans&ds._&j;
run;
%end;
data &ds._new;
merge &ds._new &mergevars;
by &byvar2;
run;
%end;
proc sql noprint;
select distinct OLD into:DROPS separated by ” ”
from &ds._MAP
where upcase(derivation)=”MANY”;
quit;
data &ds._new;
set &ds._new;
%if %symexist(drops) %then %do;
drop &drops;
%end;
run;
quit;
%mend;
%mapper(ds=LB);