HERC: Pharmacy Cost Data
Attention A T users. To access the menus on this page please perform the following steps. 1. Please switch auto forms mode to off. 2. Hit enter to expand a main menu option (Health, Benefits, etc). 3. To enter and activate the submenu links, hit the down arrow. You will now be able to tab or arrow up or down through the submenu options to access/activate the submenu links.
Search HERC

Pharmacy Cost Data


PBM Rx Database

Pharmacy Benefits Management Services (PBM) is the VA office responsible for managing the national pharmacy benefit including: Formulary Management, Pharmacy Policy, Clinical Pharmacy Practice, Academic Detailing, Clinical Pharmacy Informatics, Consolidated Mail Order Pharmacy (CMOP), Emergency Pharmacy Services, Medication Safety and Mail Order Pharmacy. VA PBM Formulary Management maintains and manages the VA’s national drug formulary process and the PBM prescription database.  

PBM developed the legacy software system/database making it available for operations and research to organize, track and analyze VA prescription data. The legacy database consisted of reports rolled up monthly from each VA facility for all outpatient dispensations, inpatient intravenous (IV) and unit dose (UD) orders. Each report was rolled up at the end of the month, drug data were standardized and aggregated into the PBM Rx Database. Upon development and release of the Corporate Data Warehouse (CDW), PBM initiated use of CDW Outpatient Rx data as the foundation of the PBM Outpatient Rx Database and conducts extraction, tracking, loading and quality assurance efforts on a weekly basis to maintain the PBM Outpatient Rx data. (The data fields/views in CDW used are readily available in the CDW metadata and on CDW’s SharePoint.) Inpatient IV and UD data are still obtained using the legacy system. These three prescription-level extracts are available and may be used by researchers: The outpatient Prescription Extract, (OPRx), IV Extract (IVRx) and Unit Dose Extract (UDRx). The three Extracts are often referred to collectively as the PBM Rx Database. They contain extensive detail on the medications prescribed and characteristics of the prescriptions including:

  • VISN: Veterans Integrated Service Network where the care was received.
  • STN_NUM: Outpatient site/station number of the facility where the prescription was ordered.
  • PAT_SSN: Patient scrambled social security number.
  • PAT_ICN: Patient Integration Control Number (ICN)
  • FRP_DATE: Original fill, refill or partial refill date.
  • REL_DATE: Date when the prescription was released from the VA pharmacy to the patient or mailed by the CMOP.
  • MONTH_KEY: Month and year the prescription was filled.
  • PRE_NUM: Unique number assigned to the prescription by the pharmacy.
  • VA_PROD: Official standardized VA name for a drug, supply or diagnostic established by PBM for formulary or non-formulary items.
  • VA_CLASS: VA drug classification of the drug, supply, or diagnostic dispensed.
  • GENERIC: Generic Drug Name
  • NRP_IND: Indicates if the prescription is a new prescription, a refill, or a partial fill.  A partial fill is where a quantity smaller than requested on the prescription is dispensed.
  • SIG: Dosing instructions printed on the prescription.
  • DISP: Total dispensed amount for a unit dose order
  • PRICE_DSP: Price of the dispense unit at the time of dispensing, i.e. the price per tablet.
  • DAY_SUPPLY: Number of days of dosing the fill will supply.
  • TL_QTY: The total quantity of the drug, supply, or diagnostic dispensed for the fill.
  • COPAY: Patient RX co-pay eligibility

Tip for calculating costs using PBM Rx: Dispensed Amount x Price per Dispensed Unit = total cost of the drug from the start date of order through stop date of order for a unit dose order.

The PBM database is not available at the Corporate Data Warehouse (CDW). Extracts are made by the PBM staff. To request an extract, a researcher must submit a summary of the study protocol, proof of Institutional Review Board (IRB) approval, and a Research Data Request form on the PBM intranet site. Visit the VHA Data Portal and select the “Pharmacy Benefits Management (PBM) Request Process” to learn more about the request process for research and operations data access (VA intranet only: http://vaww.vhadataportal.med.va.gov/DataAccess/ResearchAccess.aspx or http://vaww.vhadataportal.med.va.gov/DataAccess/OperationsAccess.aspx).



HERC would like to acknowledge the Pharmacy Benefits Management Services (PBM) team for providing the overview of the PBM Rx Database.

MCA National Pharmacy Extract

The Managerial Cost Accounting (MCA) Pharmacy (PHA) National Data Extract (NDE) provides detailed information on the drugs used by patients. It contains a single record for each pharmacy item and features a significant amount of detail on medication and dispensing details. It includes inpatient and outpatient drugs.

Pharmacy costs in MCA Outpatient (OUT) NDE: A patient who filled an outpatient prescription would be coded as having visited the "pharmacy clinic." All visits to the pharmacy clinic on the same day would be rolled into a single record.

Pharmacy costs in MCA Discharge (DISCH) NDE (inpatient): Individual inpatient prescriptions cannot be distinguished from other aspects of stays in the inpatient NDE, although there are aggregate pharmacy cost variables for the entire stay.

The cost data from the PHA NDE are included when creating the inpatient (DISCH) and outpatient (OUT) datasets.

Tip for calculating costs using the PHA NDE: cost of the drug + cost of dispensing the drug = total pharmacy cost

Visit the VHA Data Portal for access information: http://vaww.vhadataportal.med.va.gov/DataAccess/DataAccessOverview.aspx (VA intranet only). 

Learn More


CDW Pharmacy Cost Data

The CDW Pharmacy Outpatient domain contains information on the price per unit dispensed for outpatient drugs. Information can be found in the RxOut.RxOutPatFill table. Data are available for research and operations projects; data users will need to request access to CDW_Full in order to access these data. Inpatient pharmacy cost information is not available in CDW. Visit the VHA Data Portal for access information: http://vaww.vhadataportal.med.va.gov/DataAccess/DataAccessOverview.aspx (VA intranet only). 

Learn More

  • VHA Data Portal: CDW (http://vaww.vhadataportal.med.va.gov/DataSources/CDW.aspx; VA intranet only)

VA Community Care Pharmacy Data

In general, VA requires veterans to get their medications from VA. As part of the VA Community Care program, Veterans can receive short-term (less than 14 day) urgent prescriptions in the community, but long-term prescriptions must be filled by VA. Visit the VHA Office of Community Care website to learn more about Community Care benefits.

For more information about VA Community Care, visit our Community Care page.

Learn More

Choosing a Source

VA Pharmacy Data Sources
Data Availability 1999 2002 1999 1999
Drug Costs x x x Map to CDW
Labor Costs   x    
Dosing Instructions x   x Map to CDW
Unit Dispensing x   x Map to CDW
Data Types Inpatient & Outpatient Inpatient & Outpatient Outpatient Outpatient
Request Process Request through PBM DART DART DART


Checking Pharmacy Data Validity

Identifying errors in pharmacy data

Pharmacy data will inevitably contain some missing or erroneous values. Many of these can be located through simple searches based on variable values. Here are some examples:

  • National Drug Code (NDC) begins with '00000' or '99999'
  • Days Supplied and Quantity Supplied are not integers greater than 0
  • Unit or total cost = $0

Occasionally, a mismatch in units can cause an error in the cost of a drug.

There are some drugs where the dosage is in “international Units” and these are commonly expressed in multiples of 100,000. Occasionally errors occur when the price for 100,000 units is treated as the price for one unit, effectively multiplying the price by 100,000. The reverse can also happen (the price for 1 unit is treated as the price for 100,000). When the latter occurs is much less dramatic as these tend to be low-cost drugs, so it just converts a small amount (e.g., $5) to essentially zero. When the former occurs, however, a charge of $5 is converted to $500,000, which is a significant error.

There can be other errors in units, but they tend not to be as dramatic. For example, the price for a few hundred drugs, e.g., 100 or 500 pills, could be assigned to the cost of a single pill. The cause of the error is the same, but the magnitude is several orders smaller.

Addressing errors in pharmacy data

Records with these values should not be discarded without investigation; it may be possible to rectify an obvious error using other data on the record.

We recommend using PROC UNIVARIATE in SAS, or similar commands in other applications, as a baseline for checking data quality. When it is possible to focus on a small number of medications, another useful check is to calculate the implied dose per day:

Daily Dose = (# Units Dispensed * Strength per Unit) / (Days Supplied)

For example, if a 15-day supply consists of 30 pills at 200mg per pill, then the daily dose would be 400 mg/day:

400 = (30*200)/15

Package inserts may be consulted to determine standard dose ranges. Doses that are too small to be clinically meaningful or so high as to be toxic should be flagged as potentially erroneous. Investigation of individual prescription records would then be indicated, and potentially utilization records as well if available.

A potential hazard in this method is variation across facilities in the assignment of dispensed units. For example, a 50ml injection may be recorded at one facility as 50 units and at another as 1 unit. In theory the two may be reconciled based on other information on the record, such as the NDC and the dosage instructions. Variations in dispensed units are quite common. One approach to locating them is to tabulate the range of dispensed units for selected NDCs. Variation by a factor of more than 3 or 4 between greatest and least is probably a sign that further investigation is warranted.

Addressing errors in cost data

Checking the validity of cost data is likewise important but potentially complicated. Depending on when local pharmacies update their drug cost files, in theory one could observe two prices for a single NDC on the same day. Likewise, Blanket Purchase Agreements may cause disparities between two facilities on the same day for the same product. Moreover, not every local pharmacy updates its own cost file every day. What may be done to find or avoid errors then? Again, univariate statistics can alert researchers to outlying values. And cost changes for particular NDCs should not be too great. Cost variation across facilities and across time within a single facility should be moderate; extreme changes are likely signs of error.

A more comprehensive way to avoid cost errors is to use the Pharmacy Benefit Management staff's historical drug cost file to assign a standard price for each NDC covered by federal contracts. (See https://www.pbm.va.gov for contact information.) This reveals an opportunity for sensitivity analyses as well: determining the difference in outcome caused by using alternative medication cost systems. Aside from observed VA costs, choices include the optimal VA cost based on the historical cost file; federal contract prices without the "Big Four" discounts; and publicly available prices, such as a fixed percentage of AWP plus a nominal dispensing fee. (Without information on Blanket Purchase Agreements it will not be possible to recreate the optimal cost for every VA facility. Ignoring BPAs is unlikely to cause significant errors, however.)

If erroneous values are located, there are several options. Each NDC corresponds to a particular package size, thereby providing an alternative measure of units dispensed. Outlier values of dispensing unit (mg, ml, etc.) may be corrected by reference to other prescriptions with the same NDC. Cost values may be corrected by reference to the historical file created by the PBM staff or by taking an average of costs for the same NDC. As much as possible, draw data for cost corrections from the same facility and day or week.

VIReC staff performed a careful validation of the MCA National Data Extract that features prescription-level pharmacy data, comparing it to the PBM prescription-level database. The report revealed the range of errors and inconsistencies that occur in these two VA databases and shows some methods for dealing with them. The report is available on the VIReC web site.

Judgment of proper dosage requires clinical expertise. The presence of comorbid conditions or concomitant medications can lead dosages to deviate from the typical range, and many medications are started at subclinical dosages in order to detect intolerance or adverse events. Only highly unusual values should be considered suspect.

IP Number, the first five digits of the MCA (DSS) Feeder Key, is increasingly important in VA pharmacy research. Like NDC it indicates the medication dispensed. If the IP Number in a MCA pharmacy record consists of zeros or does not match the drug corresponding to the NDC, then the NDC should be used to identify the prescription.

Additional information about pharmacy data quality can be found in the archives of the HSRData listserv sponsored by VIReC. Visit https://vaww.virec.research.va.gov/Support/HSRData-L.htm to sign up for the listserv (VA intranet only).

Outlier values

HERC has developed SAS code to flag and adjust outlier observations in the MCA PHA NDE. In order to identify costs that are likely to be truly erroneous, rather than legitimately expensive medications, strict criteria are applied to define outliers. The product cost of the medication and the dispensing cost are considered separately. In consultation with MCA and after review of cost distributions by VA Class, product cost outliers are defined as costs greater than 120% of the maximum price of all drugs in the VA drug class, as shown in the drug price list from Pharmacy Benefits Management Services. This criterion is designed to select only gross outliers. Since dispensing costs are typically low and do not vary much across VA drug class, the outlier cut-off for dispensing costs is set at a fixed $100. (Note that median dispensing costs by VA drug class range from $3.56 to $9.95 in FY15.) Absolute values are used in order to identify both positive and negative outliers. Please see “How do I correct outliers in pharmacy data?” on the Data FAQ page to learn more.

Negative costs in MCA pharmacy data

The MCA Pharmacy National Data Extract (MCA PHA NDE) consists of information from three VistA data sources: Outpatient, IV and Unit Dose packages. Some records in the inpatient and outpatient MCA pharmacy data may contain negative quantity and/or cost values for prescriptions because all three packages allow returns to stock. Here are a few examples of why negative balances exist in the MCA pharmacy data:

  • The MCA VistA extracts check all records against the Patient Movement File (#405) as they are created. The Application Program Interface (API) obtains the patient's internal entry number (DFN) and date/time of the occurrence and checks to see if the patient was admitted at the time. If so, the MCA extracts mark that record as an inpatient record, otherwise they mark it as an outpatient record. If either IV or Unit Dose returns from a ward are recorded in their respective VistA Pharmacy packages after the patient has been discharged, MCA will mark the record as outpatient. This also explains why there may be a large number of negative values in the MCA outpatient pharmacy files.
  • MCA outpatient pharmacy records with negative balances may also emerge from Pharmacy IV and Unit Dose returns made on a different date than the date the prescriptions were issued. MCA creates a separate encounter for each combination of SSN + Date + Primary Stop Code. Since returns are processed at a date or time after discharge, MCA records them as an outpatient transaction. This is assuming that at large hospitals, there are separate inpatient and outpatient sections of the pharmacy. For instance, if a patient receives multiple prescriptions on a given day and one of them is returned on a different day, MCA will create two separate encounters: one from the multiple issues on the first day and a separate one for the return because the return was made on a different day.
  • Medications that are issued in individual-dose amounts to patients for consumption on the same day, such as those from the ward, are pulled a day in advance. Given that a large hospital will have large quantities of prescriptions to fill and will have to allow time for quality control, the pharmacy technician has to prepare a day’s supply of prescriptions prior to the day of patients’ consumption. Many of the ward medications are returned after the patient has been discharged. Since they process at a date/time after discharge, MCA records them as an outpatient transaction.
  • Consolidated Mail Outpatient Pharmacy (CMOP) undeliverable medications that are returned and turned back into stock also contribute to negative quantities in the MCA pharmacy data.

Negative values in MCA pharmacy data are real, and should be included in an analysis. Failure to include them will result in an overestimate of the actual costs of care.

return to top go


Last updated: February 16, 2022