Types the extracted CSVs, attaches the Texas 2036 service menu, stamps a snapshot id, and writes the analytic datasets to 02_cleaned/.
One row = one distinct negotiated price for a procedure in a network, with provider counts. run_date is the monthly-snapshot key (use it to stack months); file_vintage is the source file's production date (provenance only).
What this step produced: we read the extracted price file as text (so leading zeros in codes survive), turned the price and count columns into numbers, attached the Texas 2036 service menu (so a bare code like 99214 reads as "Office visit est pt 30-39min"), rolled each price's county up to a Public Health Region with a clean sort order, and flagged the prices that are comparable as dollars. The panel below shows the result: how many distinct prices we have, how many matched the menu and a Texas region, the comparable-price split, and five example rows.
. count 2,097,564 . di as txt "Distinct medical prices in this snapshot: " %12.0fc r(N) Distinct medical prices in this snapshot: 2,097,564 . count if !missing(service_label) 2,097,564 . di as txt " ... matched to the Texas 2036 service menu: " %12.0fc r(N) ... matched to the Texas 2036 service menu: 2,097,564 . count if !missing(region) 2,082,378 . di as txt " ... tagged with a Texas county / Public Health Region: " %12.0fc r(N) ... tagged with a Texas county / Public Health Region: 2,082,378 . di as txt _n "Comparable fee-for-service dollar prices (dollar_ffs flag): 1 = yes" Comparable fee-for-service dollar prices (dollar_ffs flag): 1 = yes . tab dollar_ffs, missing Comparable | unit price | (ffs, | dollar | basis) — | use this | filter for | $ analysis | Freq. Percent Cum. ------------+----------------------------------- 0 | 195,354 9.31 9.31 1 | 1,902,210 90.69 100.00 ------------+----------------------------------- Total | 2,097,564 100.00 . di as txt _n "Five example rows (one distinct price each):" Five example rows (one distinct price each): . list service_label procedure_code provider_type negotiated_amount region in 1/5, noobs abbreviate(24) +-----------------------------------------------------------------------------------------------------+ | service_label procedure_code provider_type negotiated_amount region | |-----------------------------------------------------------------------------------------------------| | Upper GI endoscopy diagnostic 43235 professional 423.16 | | Cystoscopy 52000 professional 85.04 | | Office visit est pt 30-39min 99214 professional 68.11 | | Simple pneumonia & pleurisy wo CC/MCC 195 institutional 0 | | Office visit new pt 45-59min 99204 professional 108.69 | +-----------------------------------------------------------------------------------------------------+
One row = one distinct drug price (NDC level), with pharmacy counts. Rx prices are PBM/plan-level (effectively national); pharmacies are not geographically filtered.
What this step produced: the same typing applied to the prescription-drug file (one row = one distinct drug price at the NDC level, with the dispensing/administrative fees alongside). Because drug pricing is set at the plan/PBM level it is effectively statewide, so there is no Texas geography column here. The panel shows the row count and the brand/generic/specialty split.
. count 1,198,555 . di as txt "Distinct drug prices in this snapshot: " %12.0fc r(N) Distinct drug prices in this snapshot: 1,198,555 . di as txt _n "By drug type:" By drug type: . capture confirm variable drug_type . if !_rc tab drug_type, missing drug_type | Freq. Percent Cum. ------------+----------------------------------- Brand | 686,771 57.30 57.30 Generic | 511,784 42.70 100.00 ------------+----------------------------------- Total | 1,198,555 100.00 . di as txt _n "Three example rows:" Three example rows: . capture list drug_name drug_type negotiated_rate dispensing_fee in 1/3, noobs abbreviate(22)
Which organizations appear in each network (raw and noisy — insurers sometimes file individual clinicians as their own tax ID). The full per-group directory is the large gzipped providers.csv.gz; decompress it only for named-provider attribution work.
What this step produced: a lookup of which organizations appear in each Texas network. It is intentionally raw — insurers sometimes file an individual clinician as their own tax ID — so treat it as a directory, not a clean provider count. The panel shows the row count and a few examples.
. count 192,438 . di as txt "Network-organization rows: " %12.0fc r(N) Network-organization rows: 192,438 . di as txt _n "Three example rows:" Three example rows: . capture list network_name provider_org_name n_group_entries in 1/3, noobs abbreviate(28)
A national Medicare allowed-amount per code, built by build_medicare_benchmark.py from CMS fee schedules (MPFS for professional, OPPS for outpatient facility, IPPS for inpatient DRGs). A code can have more than one benchmark (e.g., a professional MPFS amount AND an outpatient-facility OPPS amount); applies_to says which to use. National figures with all geographic indices set to 1.0 (first-pass simplification).
What this step produced: a national Medicare allowed amount per code, so Section 5 of the analysis can express each BCBS Texas price as a percent of Medicare. A code can carry more than one benchmark (e.g., a professional MPFS amount and an outpatient-facility OPPS amount); the applies_to column says which to use. The panel shows how many benchmark rows we built, by fee schedule.
. count 121 . di as txt "Benchmark rows: " %6.0fc r(N) Benchmark rows: 121 . di as txt _n "By Medicare fee schedule:" By Medicare fee schedule: . capture confirm variable fee_schedule . if !_rc tab fee_schedule fee_schedul | e | Freq. Percent Cum. ------------+----------------------------------- IPPS | 17 14.05 14.05 MPFS | 62 51.24 65.29 OPPS | 42 34.71 100.00 ------------+----------------------------------- Total | 121 100.00 . di as txt _n "Three example rows:" Three example rows: . capture list procedure_code fee_schedule applies_to medicare_allowed in 1/3, noobs abbreviate(26)
The three analytic tables this step writes to 02_cleaned/, with their row and variable counts. Click to view the structure of each.
. foreach f in bcbstx_prices bcbstx_rx_prices bcbstx_provider_org_directory { 2. use "${clean}/`f'.dta", clear 3. di as txt "{hline 60}" 4. di as txt "FILE: `f'.dta (rows=`=_N' vars=`=c(k)')" 5. describe, simple 6. } (BCBS Texas negotiated prices (1 row = 1 distinct price). See 300_labels.do.) ------------------------------------------------------------ FILE: bcbstx_prices.dta (rows=2097564 vars=33) source_file payer_name code_syste~r negotiated~t rate_expir~n file_vinta~d region network_name payer_type procedure_~e provider_t~e is_evergreen run_date_d region_id market_seg~t schema_ver~n procedure_~c care_setting n_provider~s service_la~l dollar_ffs county_fips file_vintage arrangement place_of_s~e n_npi_memb~s service_ca~y run_date code_system rate_basis code_modif~r n_tax_ids county_name (BCBS Texas prescription-drug negotiated prices (1 row = 1 distinct NDC price).) ------------------------------------------------------------ FILE: bcbstx_rx_prices.dta (rows=1198555 vars=17) source_file payer_type ndc administra~e service_code n_pharmac~ps run_date drug_name negotiated~e transactio~e n_price_re~s run_date_d payer_name drug_type dispensing~e historical~e n_pharmac~ns (BCBS Texas — Texas organizations by network (network-membership directory; raw).) ------------------------------------------------------------ FILE: bcbstx_provider_org_directory.dta (rows=192438 vars=5) network_name tax_id_type tax_id provider_o~e n_group_en~s