While working on the creation of SDTM datasets, we often find that the results of tests are collected in a different unit than the standard unit.
In such cases, to populate the standard results we need the conversion factors, and these are often provided in the form of an excel file or a sas dataset.
We make use of the conversion factors and programmatically convert collected results into standard results.
Below is an example of vital signs data in which some of the results are collected in non-standard units while some others are collected in standard units.
USUBJID |
VSTESTCD |
VSORRESU |
VSORRES |
VSSTRESU |
1001 |
HEIGHT |
m |
1.68 |
cm |
1001 |
WEIGHT |
LB |
138.9 |
kg |
1002 |
HEIGHT |
cm |
170 |
cm |
1002 |
WEIGHT |
kg |
60.5 |
kg |
Description of the data
And, the conversion factors are provided in the form of a sas dataset with the structure as shown below.
TESTCD |
ORRESU |
Conversion_multiplier |
HEIGHT |
m |
100 |
HEIGHT |
cm |
1 |
WEIGHT |
LB |
0.4536 |
WEIGHT |
kg |
1 |
Description of the data
To achieve this, we need to fetch the conversion factors associated with each test and unit to get a structure like this.
USUBJID |
VSTESTCD |
VSORRESU |
VSORRES |
VSSTRESU |
Conversion_multiplier |
1002 |
HEIGHT |
cm |
170 |
cm |
1 |
1001 |
HEIGHT |
m |
1.68 |
cm |
100 |
1002 |
WEIGHT |
kg |
60.5 |
kg |
1 |
1001 |
WEIGHT |
LB |
138.9 |
kg |
0.454 |
Below is an example SAS code that we can use to achieve the result.
data rvs;
infile cards truncover;
input USUBJID$ VSTESTCD$ VSORRESU$ VSORRES$ VSSTRESU$;
cards;
1001 HEIGHT m 1.68 cm
1001 WEIGHT LB 138.9 kg
1002 HEIGHT cm 170 cm
1002 WEIGHT kg 60.5 kg
;
run;
data cf;
infile cards truncover;
input TESTCD$ ORRESU$ Conversion_multiplier;
cards;
HEIGHT m 100
HEIGHT cm 1
WEIGHT LB 0.4536
WEIGHT kg 1
;
run;
proc sql;
create table rvs01 as
select a.*,b.conversion_multiplier
from rvs as a
left join
cf as b
on upcase(a.vstestcd)=upcase(b.testcd) and upcase(a.vsorresu)=upcase(b.orresu)
order by usubjid,vstestcd
;
quit;
data rvs02;
set rvs01;
vsorresn=input(vsorres,??best.);
if nmiss(vsorresn,conversion_multiplier)=0 then vsstresn=vsorresn*conversion_multiplier;
drop conversion_multiplier vsorresn;
run;
And, the final output (VSSTRESN) looks like below.
USUBJID |
VSTESTCD |
VSORRESU |
VSORRES |
VSSTRESU |
vsstresn |
1001 |
HEIGHT |
m |
1.68 |
cm |
168 |
1001 |
WEIGHT |
LB |
138.9 |
kg |
63.00504 |
1002 |
HEIGHT |
cm |
170 |
cm |
170 |
1002 |
WEIGHT |
kg |
60.5 |
kg |
60.5 |
Points to note: