Transcript Please call if we can help - Children's Hospital of

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 Children’s Hospital of Philadelphia,
METHODS and RESULTS
BACKGROUND
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 Children’s 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.
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 user=username
password=password path=‘schema';
(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.)
CHOP Siemens’
Accounting
Database
Oracle
Tables &
Views
Oracle
database
Output
SAS connects to Oracle
SAS GUI
User Input
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.
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.
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.
SAS
macros
& graph
code
SAS datasets
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.
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.
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.
ADMIN
17546
15525
14949
14150
11098
10986
10802
10477
10375
9797
YEAR 2001
DIAGNOSIS_DESC
ADMIN
5434
5138
4157
4002
3868
3438
3403
3377
3368
3307
FEVER
VOLUME DEPLETION NOS
INFECT D/T VASC DEVICE
AGRANULOCYTOSIS
HYPERTR TONSIL W ADENOID
OTHER CONVULSIONS
ESOPHAGEAL REFLUX
PNEUMONIA ORGANISM NOS
ABN RXN-ARTIFICIAL IMPL
CF W/O MECONIUM ILEUS
YEAR 2002
DIAGNOSIS_DESC
INFECT D/T VASC DEVICE
FEVER
OTHER CONVULSIONS
VOLUME DEPLETION NOS
PLEURAL EFFUSION NOS
ASTHMA NOS
HYPERTENSION NOS
IDIOPATHIC SCOLIOSIS
ABN RXN-ARTIFICIAL IMPL
PULMONARY COLLAPSE
The above table represents the administration of Acetaminophen
across different diagnosis for the year 2001 and 2002.
30000
25000
20000
Screen shot of the SAS GUI SQL query builder
15000
Text
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.
ACETAMINOPHEN
MIDAZOLAM
MORPHINE
FENTANYL
ONDANSETRON
ATROPINE
ALBUTEROL
VECURONIUM
NEOSTIGMINE
CEFAZOLIN
LIDOCAINE
OXYCODONE
PREDNISONE
DEXAMETH
RANITIDINE
DIPHENHYDRAMINE
IBUPROFEN
AMPICILLIN
KETOROLAC
PENTOBARBITAL
BUPIVACAINE
GENTAMICIN
PROPOFOL
IPRATROPIUM
GLYCOPYRR
METHOTREXATE
TACROLIMUS
10000
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.
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.
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 13:141155, 2008.
5000
0
2000
2001
2002
2003
2004
2005
2006
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.
Year
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 chair’s initiative project at CHOP.
ACKNOWLEDGMENTS
Intek Partners – provided resources for building the database