Skip to main content

AHRQ CCSRs

Overview​

Code on GitHub

Clinical Classifications Software Refined (CCSR) groups diagnosis and procedure codes into clinically meaningful categories. Developed by the Agency for Healthcare Research and Quality (AHRQ), it provides separate mappings for ICD-10-CM diagnoses and ICD-10-PCS procedures. Diagnosis categories belong to broader body systems; procedure categories belong to broader clinical domains.

Tuva Core has its own condition and procedure groupers, available in core.condition and core.procedure in the Core Data Model. This standalone mart applies AHRQ's CCSR mappings to the normalized codes in those tables for analyses that require the CCSR classification.

The package uses AHRQ's DXCCSR and PRCCSR v2027.1 mappings, released on November 10, 2026. They cover 75,915 diagnosis codes in 554 categories and 82,510 procedure codes in 328 categories.

A diagnosis code can represent several clinical concepts and therefore map to several CCSR categories. The long diagnosis table retains all of these assignments. The wide diagnosis table places categories in separate columns and records whether they occur among first-listed diagnoses, secondary diagnoses, or both.

Tuva applies these mappings without requiring an inpatient or outpatient record type. AHRQ also defines an optional output for default diagnosis assignments, which selects one category for the inpatient principal diagnosis or outpatient first-listed diagnosis. This mart does not produce that output. It preserves AHRQ's full category mappings, so a diagnosis, person, or encounter can contribute to more than one category.

The long diagnosis table retains is_ip_default_category and is_op_default_category as reference fields. They identify AHRQ's default category for each setting; they do not identify where a diagnosis occurred or filter records by setting.

Two rank fields describe different aspects of the data: diagnosis_rank is the diagnosis's position on the source record, and ccsr_category_rank is a category's position in AHRQ's mapping. A rank of 1 does not establish the principal diagnosis of an encounter: several claims can contribute first-listed diagnoses to the same encounter.

Procedure outputs include the CCSR category, clinical domain, and attributes from Core's CMS ICD-10-PCS ontology. The approach field contains the CMS approach description; it is distinct from AHRQ's open, minimally invasive, and noninvasive classification.

Output Tables

ModelDescription
ccsr__long_condition_categoryOne row per condition record and mapped diagnosis category.
ccsr__wide_condition_categoryOne row per encounter, claim, person, and data source, with diagnosis rank indicators in dxccsr_* columns.
ccsr__long_procedure_categoryOne row per procedure record and CCSR procedure category.
ccsr__wide_procedure_categoryOne row per encounter, person, and data source with binary CCSR procedure presence indicators in prccsr_* columns.
ccsr__procedure_summaryCounts of procedure records with a claim_id and approach percentages, grouped by data source, CCSR category, root operation, and approach.

Include data_source in downstream keys and joins to keep source records distinct. Both long tables retain eligible records without a CCSR match, with null category fields.

The wide tables generate their category columns when dbt runs. The data dictionary lists their fixed identifier, version, and runtime columns; the built tables also contain the generated dxccsr_* and prccsr_* columns.

Each diagnosis column encodes the category's occurrence among ranked diagnoses:

ValueMeaning
0No diagnosis with rank 1 or greater maps to the category.
1All diagnoses mapped to the category have rank 1.
2Category appears in a rank-1 diagnosis and another diagnosis with a different or missing rank.
3Category appears in a diagnosis with rank greater than 1, with no rank-1 diagnosis.

These indicators describe source diagnosis positions, not an encounter-level principal diagnosis. A category supported only by diagnoses with missing ranks has a value of 0. Use the long table to retain all mapped diagnoses regardless of rank.

Procedure columns use 0 for absent and 1 for present.

In ccsr__procedure_summary, counts represent procedure records with a non-null claim_id, so a claim with several procedures can contribute more than once. The approach_rate column reports a percentage from 0 to 100 within each data source, category, and root operation.

Installation​

Add Tuva Core and CCSR to the dbt project that maps your data to the Tuva Input Layer:

packages.yml
packages:
- git: https://github.com/tuva-health/tuva-core.git
revision: "v1.0.0"
- git: https://github.com/tuva-health/ccsr.git
revision: "v2.1.0"

Enable claims, clinical data, or both. This example enables claims:

dbt_project.yml
vars:
claims_enabled: true
clinical_enabled: false

Both wide tables are enabled by default. To omit either one, set wide_condition_enabled: false or wide_procedure_enabled: false in your project's variables.

Install the dependencies and build CCSR with its upstream models:

dbt deps
dbt build --select +package:ccsr --indirect-selection cautious

The package loads mapping snapshot 2.0.0 automatically and requests the ccsr schema, or <tuva_schema_prefix>_ccsr when configured. Your project's schema naming policy determines the final name. Adjust ccsr and core in the example queries to match your schemas.

Upgrading to CCSR 2.1

Version 2.1 removes ccsr__singular_condition_category and the record_type variable. The AHRQ v2027.1 mappings, mapping snapshot 2.0.0, and remaining output tables are unchanged from CCSR 2.0.

Update the package reference to v2.1.0, remove record_type from your project's variables, and replace downstream references to the singular model with queries against the long diagnosis table. Review the counting unit when migrating: all mapped categories are retained, so one diagnosis can contribute to several categories. The examples below demonstrate deduplication at the person and encounter levels.

Run dbt deps, then rebuild CCSR and its downstream models. dbt does not delete a table when its model is removed. After migrating dependent queries and models, drop the obsolete ccsr__singular_condition_category table from your CCSR schema.

If upgrading from CCSR 1.0, the mapping update also changes the generated wide columns. Diagnosis categories expand from 543 to 554: FAC026–FAC031, NEO075–NEO078, and SYM018–SYM019 are added, and FAC021 is removed. Procedure categories expand from 327 to 328 with the addition of ESA012. Remove overrides that retain older mapping assets or version labels, run a full refresh of CCSR, and update downstream references to changed columns.

Use Cases​

These examples use Tuva small synthetic claims and clinical data and AHRQ v2027.1 mappings. They include all mapped diagnosis categories, without selecting setting-specific defaults.

Understand Diagnosis Patterns​

Counting people with diagnoses in each body system describes the clinical needs represented in a population. It also prevents repeated visits or repeated coding of the same condition from dominating the comparison.

The query counts each person once per body system within a data source. It uses all mapped diagnoses, regardless of their position on a claim or clinical record. A person can appear in several body systems; matching identifiers from different data sources remain separate.

with people_by_body_system as (
select distinct
person_id
, data_source
, body_system
from ccsr.ccsr__long_condition_category
where ccsr_category is not null
and person_id is not null
)

select
body_system
, count(*) as person_count
from people_by_body_system
group by body_system
order by person_count desc, body_system
limit 8;
People with mapped diagnoses in each body system: endocrine, nutritional and metabolic 645; circulatory 547; musculoskeletal 462; respiratory 358; mental and neurodevelopmental 293; nervous 159; injury and poisoning 140; genitourinary 137.

The dataset contains 1,005 people with mapped diagnoses, counting each data source separately. Endocrine, nutritional, and metabolic diagnoses form the largest group, with 645 people. Body-system totals overlap and should not be added together. To examine specific conditions, count people within each CCSR category:

with people_by_category as (
select distinct
person_id
, data_source
, ccsr_category
, ccsr_category_description
from ccsr.ccsr__long_condition_category
where ccsr_category is not null
and person_id is not null
)

select
ccsr_category
, ccsr_category_description
, count(*) as person_count
from people_by_category
group by
ccsr_category
, ccsr_category_description
order by person_count desc, ccsr_category;

Compare Inpatient Utilization and Spending​

High inpatient spending can reflect frequent admissions, high payments per stay, or both. Comparing visit counts, total paid amounts, and paid amounts per visit shows how these patterns differ among stays associated with particular conditions.

The query uses all mapped diagnoses linked to an acute inpatient encounter. It deduplicates each encounter/category pair and joins to core.encounter using encounter_id and data_source. Each stay is counted once within a category. These categories describe diagnoses associated with the stay; they do not establish the reason for admission or the spending attributable to a condition.

with ccsr_encounter_category as (
select distinct
encounter_id
, data_source
, ccsr_category
, ccsr_category_description
, ccsr_parent_category
, body_system
from ccsr.ccsr__long_condition_category
where ccsr_category is not null
)

select
ccsr.ccsr_category
, ccsr.ccsr_category_description
, ccsr.ccsr_parent_category
, ccsr.body_system
, count(*) as visit_count
, sum(cast(encounter.paid_amount as decimal(18, 2))) as paid_amount
, cast(
sum(encounter.paid_amount) / nullif(count(*), 0)
as decimal(18, 2)
) as paid_per_visit
from core.encounter as encounter
left join ccsr_encounter_category as ccsr
on encounter.encounter_id = ccsr.encounter_id
and encounter.data_source = ccsr.data_source
where encounter.encounter_type = 'acute inpatient'
group by
ccsr.ccsr_category
, ccsr.ccsr_category_description
, ccsr.ccsr_parent_category
, ccsr.body_system
order by visit_count desc, ccsr.ccsr_category
limit 8;
CategoryDescriptionVisitsPaid amountPaid per visit
END011Fluid and electrolyte disorders4$21,688.67$5,422.17
SYM013Respiratory signs and symptoms4$21,735.78$5,433.95
CIR007Essential hypertension3$11,404.12$3,801.37
FAC025Other specified status3$19,554.32$6,518.11
SYM017Abnormal findings without diagnosis3$17,207.73$5,735.91
BLD001Nutritional anemia2$4,757.40$2,378.70
BLD003Aplastic anemia2$9,269.77$4,634.89
CIR019Heart failure2$15,166.05$7,583.03

Stays associated with heart failure have fewer visits than those associated with fluid and electrolyte disorders, but a higher paid amount per visit. This comparison describes groups of stays that may overlap.

The full cohort contains 7 acute inpatient visits and $33,335.43 paid. All seven visits belong to multiple categories, so do not sum visits or payments across category rows. The left join retains encounters without a mapped diagnosis in a null-category group; limit 8 returns the eight groups with the most visits.

To analyze emergency department visits, change the encounter filter to encounter.encounter_type = 'emergency department'. Keep the same deduplication and join keys.

Describe the Procedure Mix​

Procedure mix describes how activity is distributed across clinical services. Comparing that distribution across facilities or over time can reveal differences in service volume and help guide capacity planning.

The query counts only procedure records coded in ICD-10-PCS, using the long procedure table to group them by clinical domain.

select
clinical_domain
, count(*) as procedure_count
from ccsr.ccsr__long_procedure_category
group by clinical_domain
order by procedure_count desc, clinical_domain
limit 8;
Synthetic procedure counts: gastrointestinal 274; musculoskeletal, subcutaneous tissue and fascia 180; general region 91; therapeutic substances 86; cardiovascular 86; hepatobiliary and pancreas 86.

The dataset contains 803 ICD-10-PCS procedure records across six clinical domains. Gastrointestinal procedures are the largest group, with 274 records, followed by musculoskeletal, subcutaneous tissue, and fascia procedures with 180.

For more detail within a domain, group by ccsr_category and ccsr_category_description. Use the wide procedure table to identify encounters containing a category, or ccsr__procedure_summary to examine counts of procedure records with a claim_id and their approach percentages.