HERC: Pharmacy 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.

Pharmacy Data


PBM Rx Database

Pharmacy Benefits Management Services (PBM) is the Department of Veterans Affairs (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 along with a broad set of other activities related to pharmacy including: purchasing, prime vendor contracts, clinical guidelines, criteria for use, prior authorization, drug recall initiatives, medication safety and pharmacy outcomes research.  

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 Fileman menu 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.
  • 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.
  • SCRSSN: Scrambled social security number.
  • 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.
  • 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.
  • TL_COST: Total cost of a prescription
  • COPAY: Patient RX co-pay eligibility

The PBM database is not available at the Austin Information Technology Center (AITC) or 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 System (MCA), formerly Decision Support System (DSS), is a management information system that tracks health care utilization (workload) and assigns an approximate cost to each encounter and service. Each VA facility has a separate implementation of MCA. Extracts from each facility are combined into national-level datasets for use by VA managers and researchers. The National Data Extracts (NDEs) are organized at the level of outpatient clinic encounters, inpatient stays (or inpatient bedsection-months), laboratory tests, and pharmacy prescriptions. Both inpatient and outpatient records are available. 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. Individual prescriptions for inpatients cannot be distinguished from other aspects of stays in the inpatient NDE, although there are aggregate pharmacy cost variables for the entire stay. The National Pharmacy Extract contains a single record for each pharmacy item. It features a significant amount of detail on medication and dispensing details. HERC and VIREC have each documented MCA files. All MCA data are available in CDW. Operations users can request access to data using the VHA NDS Access for Health Operations ePAS Form. Researchers can request data through the DART research request process. For more information, visit the VHA Data Portal (VA Intranet only: http://vaww.vhadataportal.med.va.gov/).

PHA (Pharmacy). This pharmacy NDE provides detailed information on the drugs used by patients. It includes inpatient and outpatient drugs. Cost details are included; these cost data are included when creating the inpatient (DISCH) and outpatient (OPAT) datasets.

Learn More

MCA Intermediate Product Department Pharmacy Data

The MCA production data feature "intermediate products," the individual supplies, procedures, and labor effort that together compose a single service or encounter. (For example, intermediate product #3850 is "general surgical service general anesthesia time," representing one aspect of a surgery encounter that required general anesthesia.) In FY13 MCA created the PROD (IP) NDE, which summarizes cost and workload information at the Intermediate Product (IP) level. This data is available from FY13 to the present.

Learn More

VistA's Computerized Patient Record System (CPRS)

VistA is the primary repository of clinical and administrative data in VA. It consists of computer systems at each VA medical center and the national network that links them. Within each VISTA implementation is a large number of separate 'modules' or 'packages' designed to store data on a particular subject and to produce management reports. VistA contains all clinical data generated in VA facilities, including inpatient and outpatient stays, laboratory tests, prescriptions, and dentistry. It also features certain types of administrative data, such as drug prices. There is no prescription or pharmacy database per se within VistA. Prescription-level data from a single facility can be obtained by creating an extract from the "pharmacy package" and other modules of the local system. Both inpatient and outpatient pharmacy data are available. Only direct costs can be extracted directly; indirect costs would have to be estimated.

Learn More

  • VIReC Resource Guide: VistA (VA intranet only: https://vaww.virec.research.va.gov/VistA/RG-VistA-CY12-RA.pdf)
  • VHA Data Portal: CPRS (VA intranet only: http://vaww.vhadataportal.med.va.gov/ToolsApplications/CPRS.aspx)

VA Community Care Pharmacy Data

The VA Community Care files include information on covered care provided to VA patients by contract providers under the VA Community Care Program, including care provided under the Veterans Access, Choice, and Accountability Act (VACAA; also called "Choice," FY2015-2017) and the VA Maintaining Internal Systems and Strengthening Integrated Outside Networks (MISSION) Act (FY2019-present).

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

Questions to consider when choosing a pharmacy data source include the following: What data elements do I need? How much time and effort can I spend on obtaining the data? What other types of data need to be linked to the pharmacy data? Researchers needing details of the medication and prescription will need to use the MCA Pharmacy Extract, the PBM database, or VistA. Obtaining VistA data is significantly more difficult and thus is only advisable when it is the only source of needed information. There is also a trade-off between time and money. VistA data are free but require special permissions and programs. PBM data are extracted by the PBM SHG staff and so require relatively little effort, but funded studies will be charged for their assistance. MCA is free but has relatively little overlap with PBM on clinical or cost variables.

In general only VA employees may directly access these pharmacy data sources. The PBM SHG will create an extract from the PBM V3.0 Database for non-VA researchers only if those researchers collaborate with a VA employee or belong to an official oversight body. The timeshare accounts needed to access MCA data are restricted to VA employees, as is direct access to VistA systems. In sum, non-VA researchers will need to find a VA collaborator in order to use VA pharmacy data.

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 error 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-L listserv sponsored by VIReC. To sign up visit the VIReC web site, https://www.virec.research.va.gov.

Outlier values

HERC has developed SAS code to flag and adjust outlier observations in the Managerial Cost Accounting Pharmacy National Data Extract (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 Ask HERC page to learn more.

Negative costs in MCA pharmacy data

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.

HERC Resources


Last updated: February 05, 2021