NESUG 2007 Applications Big and Small

MedDRA Dictionary: Reporting Version Updates

Using SAS® and Excel

Richard Zhou, Johnson & Johnson Pharmaceutical Research and Development, L.L.C
Denis Michel, Johnson & Johnson Pharmaceutical Research and Development, L.L.C

ABSTRACT

MedDRA (Medical Dictionary for Regulatory Activities) is widely used to report adverse event data in clinical
trials, as well as pharmaceutical post-marketing safety information. The dictionary is managed by the MSSO
(Maintenance and Support Services Organization). New versions are released twice a year from the MSSO.
The updated versions contain added records and modifications to previous records. A primary key variable,
containing a unique code, is maintained across versions.

Comparing older versions of MedDRA with newer versions, finding out and listing any new and important
changes or additions is very useful to people who work in related fields. This paper presents a simple and
efficient way to compare new and old versions of MedDRA. SAS code is used to create an Excel workbook
containing formatted worksheets through automation.

INTRODUCTION

MedDRA is distributed in ASCII file format downloadable from the MSSO Web site. For clinical programming
needs, usually we convert it to a SAS data set. The structure of the SAS MedDRA dictionary data set includes
the following main elements:
• SOC_NAME - System Organ Class name
• HLGT_NAME - High-Level Group Term
• HLT_NAME - High-Level Term
• PT_NAME - Preferred Term
• LLT_NAME and LLT_CODE - Lowest-Level Term and code

Additionally for each LLT, it also contains information such as

• PRIMARY_ - A flag of primary SOC (Y/N)
• LLT_CURR – A flag of LLT currency (Y/N for Current/Non-current)

The lowest level term code (LLT_CODE) is the primary key of the data set, from which all dictionary variables in
the hierarchy can be determined. It contains a unique code that is retained across all versions of the dictionary.
The lowest level term text (LLT_NAME) may be changed, but the code remains. If a code is no longer to be
used, the code is not deleted. Instead, the record is updated with a flag that the lowest level term is not current
(LLT_CURR = “N”). Since the lowest level code is the primary key variable and the codes are not changed or
deleted across dictionary versions, this variable can be used as the BY variable to compare the other variables
in different dictionary versions.

STRATEGY

To compare older versions of MedDRA with newer versions, generally we select the primary path SOC in both
the newer and older versions (i.e. PRIMARY_=”Y”). We are interested on following cases:
1. Confirm that all LLT_CODE’s in the old version have been included in the new version. If not, then
output LLT codes not in the new version. As stated above, lowest level term codes should not be
deleted in new dictionary versions.
2. Locate and output any LLT_CODE in the new version that are not in the old version.
3. Determine and output any changes in LLT_NAME for an identical LLT_CODE.
4. Determine and output any changes in LLT_CURR for identical LLT_CODE. If it is changed, there are
two possibilities:

1
NESUG 2007 Applications Big and Small

a. from current (in an old version) to non-current (in the new version), or, the reverse,
b. from non-current (in an old version) to current (in the new version).
5. Locate and output any changes in PT_NAME for identical LLT_CODE
6. Locate and output any changes in SOC_NAME for identical LLT_CODE.

To get results for above seven cases (including 4a and 4b), we need to merge newer and older versions of the
MedDRA data set by key variable LLT_CODE and compare the matched variables LLT_NAME, LLT_CURR,
PT_NAME and SOC_NAME. We need to process as follows.
1. Select PRIMARY_=”Y” to filter both two data sets
2. Rename LLT_NAME, LLT_CURR, PT_NAME and SOC_NAME in the older version of the MedDRA
data set to new variable names so that we can distinguish between the older and newer versions.

In processing the comparisons, we output seven SAS data sets (DropLLT, NewLLT, ChgLLT, Noncurrent,
Current, PT, and SOC), each containing the observations corresponding to the specific category. From these
SAS data sets, we can also generate an additional summary data set (SUMMARY) containing the observation
counts in each specific category.

Finally, we use SAS to generate an Excel output file to report updated information based on comparing the old
and new MedDRA dictionary data sets. The seven SAS data sets created above, if they contain any
observations, are converted to Excel worksheets in an Excel workbook. The Excel workbook includes multiple
worksheets (corresponding to each category of differences that exists). The advantage of using Excel
worksheets instead of SAS listings or rich text files is that we can quickly look at a comparison summary and
easily find listings in any specific worksheet. SAS version 9 provides a very powerful tool, the LIBNAME engine
for Microsoft Excel. This tool makes the process of converting SAS data to Excel format very simple and quick.
The SAS data sets are written to individual Excel worksheets in an Excel workbook.

EXAMPLE

In the following example, assume we want to compare new MedDRA version 10.0 (data set name: mdra100) with
old MedDRA version 9.1 (data set name: mdra91).

libname dict "C:\MedDRA";

%let old=91;
%let new=100;

*********************************************************************************;
* 1 Sort the old and new versions of MedDRA data by LLT_CODE(Lowest-Level code) *;
* 2 Filter data by PRIMARY_=”Y” (primary path SOC=Yes) *;
* 3 Keep only selected variables in both data sets *;
*********************************************************************************;

proc sort data= dict.mdra&old.(keep=llt_code llt_name llt_curr pt_name soc_name

primary_)
out=mdra_old(drop=primary_);
where primary_='Y';
by llt_code;
run;

proc sort data= dict.mdra&new.(keep=llt_code llt_name llt_curr pt_name soc_name

primary_)
out=mdra_new(drop=primary_);
where primary_='Y';
by llt_code;
run;

2
NESUG 2007 Applications Big and Small

*************************************************************************;
* 1 Merge the data sets by LLT_CODE *;
* 2 Rename LLT_NAME, LLT_CURR, PT_NAME, SOC_NAME to be different names *;
* in the old data set to distinguish in comparisons *;
* 3 Find differences for each pair of variables *;
*************************************************************************;

data DropLLT
ChgLLT
NewLLT(keep=llt_code llt_name llt_curr pt_name soc_name)
Noncurrent(keep=llt_code llt_name llt_curr p_curr)
Current(keep=llt_code llt_name llt_curr p_curr)
PT(keep=llt_code llt_name pt_name p_ptnm soc_name p_socnm)
SOC(keep=llt_code llt_name pt_name p_ptnm soc_name p_socnm);

***This nonexecutable informat statement is used to re-ordre variables;

informat llt_code llt_name llt_curr p_curr pt_name p_ptnm soc_name p_socnm;

merge mdra_old(in=in_old rename=(llt_name=p_lltnm llt_curr=p_curr pt_name=p_ptnm

soc_name=p_socnm))
mdra_new(in=in_new);
by llt_code;

if in_old and ^in_new then output DropLLT; /*Should be zero obs*/

else if in_new and ^in_old then output NewLLT;
else do; /* LLTs in old and new versions */
if llt_name^=p_lltnm then output ChgLLT;
if llt_curr='N' and p_curr='Y' then output Noncurrent;
else if llt_curr='Y' and p_curr='N' then output Current;
if pt_name^=p_ptnm then output PT;
if soc_name^=p_socnm then output SOC;
end;

format llt_code 10.;

label p_lltnm='Previous LLT name'
p_curr='Previous LLT currency'
p_ptnm='Previous PT name'
p_socnm='Previous SOC name';
run;

**********************************************************;
* Assign macro variable names for each SAS data set name *;
* These will be used as Excel worksheet names *;
**********************************************************;

%let ds1=DropLLT;
%let ds2=NewLLT;
%let ds3=ChgLLT;
%let ds4=Noncurrent;
%let ds5=Current;
%let ds6=PT;
%let ds7=SOC;

3
NESUG 2007 Applications Big and Small

***********************************************************;
* Get the number of observations for data set &ds1 - &ds7 *;
***********************************************************;
%macro ck_obs;
%do i=1 %to 7;
proc contents data=&&ds&i out=x(keep=nobs) noprint;
proc sort data=x nodupkey;
by nobs;
data _null_;
set x;
%global obs_&i;
call symput('obs_'||"&i", compress(left(nobs)));
run;
%put &&obs_&i;
%end;
%mend ck_obs;

%ck_obs;

****************************************************;
* Assign new macro variable name OLDVER and NEWVER *;
* (so that if &old=91, then &oldver=9.1 *;
* and if &new=100, then &newver=10.1 ) *;
****************************************************;
data _null_;
call symput('oldver',trim(left(put(&old/10,4.1))));
call symput('newver',trim(left(put(&new/10,4.1))));
run;

**********************************************************;
* Create the data set SUMMARY for a cover page. *;
* Provide print instructions for the workbook. *;
* Write the number of observations in each data set. *;
* The summary data set will be converted to an Excel *;
* worksheet that summarizes the comparison. *;
**********************************************************;

%macro cvpage;
data summary;
length NOTE $100.;
NOTE="To print the entire workbook:"; output;
NOTE="From the File Menu select Print"; output;
NOTE="Choose Entire Workbook in the lower left hand corner"; output;
NOTE="Press OK - to Print the entire workbook"; output;
NOTE=' ';output;
NOTE="Summary of MedDRA Changes"; output;
NOTE="Version &oldver to &newver"; output;
NOTE=' ';output;

%do i=1 %to 7;

%if &i=1 %then %do;
NOTE="&obs_1 LLTs dropped from &oldver to &newver";
%end;
%if &i=2 %then %do;

4
NESUG 2007 Applications Big and Small

NOTE="&obs_2 LLTs added from &oldver to &newver";

%end;
%if &i=3 %then %do;
NOTE="&obs_3 LLTs changed text in name";
%end;
%if &i=4 %then %do;
NOTE="&obs_4 LLTs changed to noncurrent";
%end;
%if &i=5 %then %do;
NOTE="&obs_5 Noncurrent LLTs changed to current";
%end;
%if &i=6 %then %do;
NOTE="&obs_6 LLTs mapped to different preferred terms";
%end;
%if &i=7 %then %do;
NOTE="&obs_7 LLTs mapped to different system orgam classes";
%end;

%if &&obs_&i^=0 %then %do;

NOTE=trim(NOTE)||" (See &&ds&i worksheet)";
%end;

output;
%end;
run;

%mend cvpage;

%cvpage;

** assign ds0 as the macro variable name for the SUMMARY data **;
** Summary will be the name of the converted Excel worksheet **;
%let ds0=Summary;

** Set obs# > 0 for the SUMMARY data so that it is output to Excel **;
%let obs_0=1;

*************************************************************************;
* Use LIBNAME engine in MS Excel to generate an output report file *;
* Convert SAS data sets to Excel worksheets and store in one workbook *;
* (No worksheet to be created if the SAS data set has 0 observations) *;
*************************************************************************;

** Assign xlsfile as the macro variable name for the output Excel file **;
** The descriptive file name includes the old and new versions compared**;
%let xlsfile=MedDRA_Changes_&old._&new;

** Define a LIBNAME for the directory of the output Excel file **;
** The Excel file is a workbook containing multiple worksheets **;
libname WrkBk EXCEL "C:\MedDRA\&xlsfile..xls" ver=2002;

5
NESUG 2007 Applications Big and Small

*************************************************************************;
** Macro mdxls writes the summary Excel worksheet and any of the seven **;
** SAS data sets that contain any observations to additional Excel **;
** worksheets in a single Excel workbook named above **;
*************************************************************************;

%macro mdxls;
%do i=0 %to 7;
%if &&obs_&i^=0 %then %do;
data WrkBk.&&ds&i;
set &&ds&i;
run;
%end;
%end;
libname WrkBk clear;
quit;
%mend mdxls;

%mdxls;

The output report file is showing in the figure 1 - 6 below.

6
NESUG 2007 Applications Big and Small

Figure 1. Summary Sheet

Figure 2. NewLLT Sheet (Listing of new LLT_CODEs)

Figure 3. ChgLLT Sheet (Listing of changes in LLT_NAME)

7
NESUG 2007 Applications Big and Small

Figure 4. Noncurrent Sheet (Listing of changes from current to non-current in LLT_CURR)

Figure 5. PT Sheet (Listing of changes in PT_NAME)

8
NESUG 2007 Applications Big and Small

Figure 6. SOC Sheet (Listing of changes in SOC_NAME)

As previously mentioned, the MedDRA dictionary is usually released by MSSO every six months. To get an
updated report file, we only need to update the version numbers (%let old= ; %let new=;) and run the program.

CONCLUSION

This paper describes an efficient strategy for comparing MedDRA dictionary versions using the SAS system to
generate output in Excel format.

Using an Excel workbook with multiple worksheets as the output report file is a practical selection, making it
easy and fast to check any specific results. The SAS LIBNAME Engine for Excel provided a simple but very
powerful tool to generate the output report with complete automation.

REFERENCES

[1] http://www.meddramsso.com/MSSOWeb/index.htm
[2] http://www.codingplus.com/faq.cfm
[3] Choate, Paul and Martell, Carol (2006), “De-Mystifying the SAS® LIBNAME Engine in Microsoft Excel: A
Practical Guide”

CONTACT INFORMATION

Your comments and questions are valued and encouraged. Contact the authors at:

Richard Zhou
Johnson & Johnson Pharmaceutical Research and Development, L.L.C.
920 Route 202
PO Box 300
Raritan, NJ 08869
Email: rzhou@prdus.jnj.com

9
NESUG 2007 Applications Big and Small

Denis Michel
Johnson & Johnson Pharmaceutical Research and Development, L.L.C.
1125 Trenton-Harbourton Road
PO Box 200
Titusville, NJ 08560
Email: dmichel@prdus.jnj.com

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of
SAS
Institute Inc. in the USA and other countries. ® indicates USA registration.

MedDRA® is a registered trademark of the International Federation of Pharmaceutical

Manufacturers and Associations (IFPMA).

Other brand and product names are trademarks of their respective companies.

10