Drug utilization forms an important aspect of many research initiatives. It helps researchers to examine the effectiveness of pharmacotherapy applied in the inpatient, outpatient and various other settings. In the arena of pediatric pharmacotherapy, drug

1
A SAS-Based Query and Exploitation Interface to
Hospital Drug Utilization Bhuvana Jayaraman and
Jeffrey S. Barrett Laboratory for Applied PK/PD,
Division of Clinical Pharmacology, The Childrens
Hospital of Philadelphia,
BACKGROUND
METHODS and RESULTS
Drug utilization forms an important aspect of
many research initiatives. It helps researchers
to examine the effectiveness of pharmacotherapy
applied in the inpatient, outpatient and various
other settings. In the arena of pediatric
pharmacotherapy, drug utilization can be used to
examine various outcomes and the extent to which
best practices in children differ from labeling,
adult dosing guidance etc. The Childrens
Hospital of Philadelphia (CHOP) and its satellite
locations handle more than 15,000 inpatient
admissions and more than 200,000 emergency and
outpatient visits each year. CHOP has 304 beds,
20 of which are allocated to intensive care.
Physicians at CHOP administer drugs based on the
CHOP formulary. The entire CHOP formulary
consists of 998 drugs with 7,553 formulation-dose
entries (unique formulations and dosage
strengths) at present. As part of the continuous
review of the therapeutic standards committee,
the formulary is periodically re-evaluated and
drugs are added or removed from the list. The
Hospital Drug Utilization (HDU) HDU was created
at CHOP using the accounting data from the
SIEMENs' accounting system. The raw data
extracted from the accounting system is in the
form of text files running to 80,000 rows. The
database that hosts the HDU is a relational
database running on Oracle 9i. Data is loaded
into Oracle database based on a star schema
data model. This data model enables easy
analysis, retrieval, and reporting. The central
table, commonly referred to as the fact table,
is where all key facts, measurements, or results
are stored. This table is connected to a set of
attribute tables, commonly referred to as
dimension tables, through which the fact tables
are indexed or defined. The data that resides on
the HDU spans 6 years from 2001 to 2006 and
contains over a million records. The main fact
tables are diagnosis, treatment, admission and
procedure related to patients. These tables get
appended every year. The other dimension tables
get appended or updated as and when there is a
change in the diagnosis code or treatment code or
a procedure code. The number of records in a
single fact table is about 500,000 and sometimes
exceeds that - for e.g.. Patient Treatment has
about 11,700,000 rows. Usage at CHOP Over the
years, HDU has been used for various purposes. To
name a few, we have queried the database to get
information regarding utilization of various
drugs at CHOP across various age groups,
utilization of drug for various
diagnosis/treatment, comparison of drug
utilization across hospitals. For all the above
analysis, data was collected from the database
through SQL queries. The data analysis, data
summarizations, statistical analysis and graphs
were done using SAS. SAS is an excellent data
management and statistical analysis tool.
SAS/Access, SAS/ODS and other graphical utilities
are used in this interface and this is explained
in detail in the methods section. Objectives Our
objective was to design an interface that would
allow both advanced programmers and
non-programmers to exploit the utility of SAS for
specific projects.
Base SAS, SAS Macros, SAS/ACCESS and SAS GUI were
used to achieve the objective. Connection to the
Oracle database was done using the LIBNAME
statement which uses the Oracle engine to provide
direct and dynamic access to the oracle
data. libname mydblib oracle userusername
passwordpassword pathschema' (Note The
values for user, password and path are specific
to Oracle. The value for path statement is the
Oracle alias name in the Oracle TNSNAMES.ORA
file.)
Users can also input more than one drug or more
than a year. The code utilizes PROC SQL
capability to merge the datasets. The code also
has the functionality to check for errors such
that no empty dataset is returned.
The PDF output from SAS using the SAS ODS (Output
Delivery System) shows the summary statistics for
the SMA patients treated at CHOP.
The flowchart describing the process of the data
flow from the Siemens system to Oracle database
to SAS.
With both the GUI interface and the user
interface, users have the opportunity to get
their data in the format that they desire.
Proficient SAS programmers can create and run
their own code. Other users can almost get all
the data that they require using the user
interface.
Screen shot of the view of the SAS user interface
SAS reporting and Graphing functions are
extremely versatile. The SAS Output Delivery
System (ODS) has the capability to print data in
an HTML or PDF or RTF file format. The PROC GPLOT
can create a JPEG or WMF file as is required by
the user.
SAS GUI to build SQL queries SAS GUI has the
power to build SQL queries. Under the
Solutions?Desktop?Reporting?Query Data, SQL
Queries can be constructed by users who have good
grip on SQL commands. This tool helps user to
create new SAS datasets. After creation of SAS
datasets, they can run their own program or can
use the macros available to create customized
report/graph.
CONCLUSIONS

• SAS is powerful data analysis tool. The ease of
  using SAS is exploited in this interface. This
  tool aids researchers to utilize the Drug
  Utilization data for future research. This tool
  has been used for the following
• To prioritize choice of agents for a
  Pharmacotherapy clinical
• application Pediatric Knowledge Base, in
  development at CHOP.
• Recommendation to do a clinical trial of
  Acetaminophen IV based
• on the administration and usage of
  Acetaminophen at CHOP.
• To work on a disease progression model for Spinal
  Muscular
• Atrophy patients.
• The future plan is to make this interface a web
  interface using SAS IntrNet.

The above table represents the administration of
Acetaminophen across different diagnosis for the
year 2001 and 2002.

REFERENCES

• Zuppa AF, Vijayakumar S, Mondick JT, Pavlo P,
  Jayaraman B, Patel D, Narayan M, Boneva T,
  Vijayakumar K, Adamson PC, Barrett JS. Design
  and implementation of a web-based hospital drug
  utilization system. J Clin Pharmacol 47(9)
  1172-1180, 2007.
• Barrett JS, Patel D, Jayaraman B, Narayan M,
  Zuppa A. Key Performance Indicators for the
  Assessment of Pediatric Pharmacotherapeutic
  Guidance. J Pediatr Pharmacol Ther 13141-155,
  2008.
• Zuppa AF, Jayaraman B, Blumer J, Reed M, Barrett
  JS. Cross-Institutional Drug Utilization In
  Pediatric Intensive Care Units.The Journal of
  Clinical Pharmacology 2006 46 (9) 1061.

Screen shot of the SAS GUI SQL query builder
Customized user input and output A user
interface was designed to solicit user input.
Windows was used to collect the user input. This
acts similar to a HTML form. The variables are
collected as a macro variables and are then used
in SAS code to get data from the SAS datasets.
Based on the user input, necessary tables are
merged and results/graphs are designed. User can
ask for summary data, year specific data, drug
specific data etc. Complexity arises when user
requests more than one drug or would like to see
only seasonal changes in the data. Depending on
the user request, the window will be displayed
again to collect more data.
ACKNOWLEDGMENTS
The overall ranking of top 25 drugs from the HDU
across all years with the inclusion of
Methotrexate (MTX) and Tacrolimus (TAC). MTX and
TAC were chosen as the dashboard drugs for the
Pediatric Knowledge Base a chairs initiative
project at CHOP.
Intek Partners provided resources for building
the database