Table cms.mbsf_abcd_2017

Overview for cms.mbsf_abcd_2017

Primary Key: FILE, RECORD

SQL/DDL Statement

CREATE TABLE  cms.mbsf_abcd_2017 (
	BENE_ID VARCHAR(15),
	RFRNC_YR INT,
	ENRL_SRC VARCHAR(3),
	SAMPLE_GROUP VARCHAR(2),
	EFIVEPCT VARCHAR(1),
	CRNT_BIC VARCHAR(2),
	STATE_CD VARCHAR(2),
	CNTY_CD VARCHAR(3),
	ZIP_CD VARCHAR(5),
	STATE_CNTY_FIPS_CD_01 VARCHAR(5),
	STATE_CNTY_FIPS_CD_02 VARCHAR(5),
	STATE_CNTY_FIPS_CD_03 VARCHAR(5),
	STATE_CNTY_FIPS_CD_04 VARCHAR(5),
	STATE_CNTY_FIPS_CD_05 VARCHAR(5),
	STATE_CNTY_FIPS_CD_06 VARCHAR(5),
	STATE_CNTY_FIPS_CD_07 VARCHAR(5),
	STATE_CNTY_FIPS_CD_08 VARCHAR(5),
	STATE_CNTY_FIPS_CD_09 VARCHAR(5),
	STATE_CNTY_FIPS_CD_10 VARCHAR(5),
	STATE_CNTY_FIPS_CD_11 VARCHAR(5),
	STATE_CNTY_FIPS_CD_12 VARCHAR(5),
	AGE INT,
	BENE_DOB DATE,
	V_DOD_SW VARCHAR(1),
	DEATH_DT DATE,
	SEX VARCHAR(1),
	RACE VARCHAR(1),
	RTI_RACE_CD VARCHAR(1),
	COVSTART DATE,
	OREC VARCHAR(1),
	CREC VARCHAR(1),
	ESRD_IND VARCHAR(1),
	MDCR_STUS_CD_01 VARCHAR(2),
	MDCR_STUS_CD_02 VARCHAR(2),
	MDCR_STUS_CD_03 VARCHAR(2),
	MDCR_STUS_CD_04 VARCHAR(2),
	MDCR_STUS_CD_05 VARCHAR(2),
	MDCR_STUS_CD_06 VARCHAR(2),
	MDCR_STUS_CD_07 VARCHAR(2),
	MDCR_STUS_CD_08 VARCHAR(2),
	MDCR_STUS_CD_09 VARCHAR(2),
	MDCR_STUS_CD_10 VARCHAR(2),
	MDCR_STUS_CD_11 VARCHAR(2),
	MDCR_STUS_CD_12 VARCHAR(2),
	A_TRM_CD VARCHAR(1),
	B_TRM_CD VARCHAR(1),
	A_MO_CNT INT,
	B_MO_CNT INT,
	BUYIN_MO INT,
	HMO_MO INT,
	PTD_MO INT,
	RDS_MO INT,
	DUAL_MO INT,
	BUYIN01 VARCHAR(1),
	BUYIN02 VARCHAR(1),
	BUYIN03 VARCHAR(1),
	BUYIN04 VARCHAR(1),
	BUYIN05 VARCHAR(1),
	BUYIN06 VARCHAR(1),
	BUYIN07 VARCHAR(1),
	BUYIN08 VARCHAR(1),
	BUYIN09 VARCHAR(1),
	BUYIN10 VARCHAR(1),
	BUYIN11 VARCHAR(1),
	BUYIN12 VARCHAR(1),
	HMOIND01 VARCHAR(1),
	HMOIND02 VARCHAR(1),
	HMOIND03 VARCHAR(1),
	HMOIND04 VARCHAR(1),
	HMOIND05 VARCHAR(1),
	HMOIND06 VARCHAR(1),
	HMOIND07 VARCHAR(1),
	HMOIND08 VARCHAR(1),
	HMOIND09 VARCHAR(1),
	HMOIND10 VARCHAR(1),
	HMOIND11 VARCHAR(1),
	HMOIND12 VARCHAR(1),
	PTC_CNTRCT_ID_01 VARCHAR(5),
	PTC_CNTRCT_ID_02 VARCHAR(5),
	PTC_CNTRCT_ID_03 VARCHAR(5),
	PTC_CNTRCT_ID_04 VARCHAR(5),
	PTC_CNTRCT_ID_05 VARCHAR(5),
	PTC_CNTRCT_ID_06 VARCHAR(5),
	PTC_CNTRCT_ID_07 VARCHAR(5),
	PTC_CNTRCT_ID_08 VARCHAR(5),
	PTC_CNTRCT_ID_09 VARCHAR(5),
	PTC_CNTRCT_ID_10 VARCHAR(5),
	PTC_CNTRCT_ID_11 VARCHAR(5),
	PTC_CNTRCT_ID_12 VARCHAR(5),
	PTC_PBP_ID_01 VARCHAR(3),
	PTC_PBP_ID_02 VARCHAR(3),
	PTC_PBP_ID_03 VARCHAR(3),
	PTC_PBP_ID_04 VARCHAR(3),
	PTC_PBP_ID_05 VARCHAR(3),
	PTC_PBP_ID_06 VARCHAR(3),
	PTC_PBP_ID_07 VARCHAR(3),
	PTC_PBP_ID_08 VARCHAR(3),
	PTC_PBP_ID_09 VARCHAR(3),
	PTC_PBP_ID_10 VARCHAR(3),
	PTC_PBP_ID_11 VARCHAR(3),
	PTC_PBP_ID_12 VARCHAR(3),
	PTC_PLAN_TYPE_CD_01 VARCHAR(3),
	PTC_PLAN_TYPE_CD_02 VARCHAR(3),
	PTC_PLAN_TYPE_CD_03 VARCHAR(3),
	PTC_PLAN_TYPE_CD_04 VARCHAR(3),
	PTC_PLAN_TYPE_CD_05 VARCHAR(3),
	PTC_PLAN_TYPE_CD_06 VARCHAR(3),
	PTC_PLAN_TYPE_CD_07 VARCHAR(3),
	PTC_PLAN_TYPE_CD_08 VARCHAR(3),
	PTC_PLAN_TYPE_CD_09 VARCHAR(3),
	PTC_PLAN_TYPE_CD_10 VARCHAR(3),
	PTC_PLAN_TYPE_CD_11 VARCHAR(3),
	PTC_PLAN_TYPE_CD_12 VARCHAR(3),
	PTDCNTRCT01 VARCHAR(5),
	PTDCNTRCT02 VARCHAR(5),
	PTDCNTRCT03 VARCHAR(5),
	PTDCNTRCT04 VARCHAR(5),
	PTDCNTRCT05 VARCHAR(5),
	PTDCNTRCT06 VARCHAR(5),
	PTDCNTRCT07 VARCHAR(5),
	PTDCNTRCT08 VARCHAR(5),
	PTDCNTRCT09 VARCHAR(5),
	PTDCNTRCT10 VARCHAR(5),
	PTDCNTRCT11 VARCHAR(5),
	PTDCNTRCT12 VARCHAR(5),
	PTDPBPID01 VARCHAR(3),
	PTDPBPID02 VARCHAR(3),
	PTDPBPID03 VARCHAR(3),
	PTDPBPID04 VARCHAR(3),
	PTDPBPID05 VARCHAR(3),
	PTDPBPID06 VARCHAR(3),
	PTDPBPID07 VARCHAR(3),
	PTDPBPID08 VARCHAR(3),
	PTDPBPID09 VARCHAR(3),
	PTDPBPID10 VARCHAR(3),
	PTDPBPID11 VARCHAR(3),
	PTDPBPID12 VARCHAR(3),
	SGMTID01 VARCHAR(3),
	SGMTID02 VARCHAR(3),
	SGMTID03 VARCHAR(3),
	SGMTID04 VARCHAR(3),
	SGMTID05 VARCHAR(3),
	SGMTID06 VARCHAR(3),
	SGMTID07 VARCHAR(3),
	SGMTID08 VARCHAR(3),
	SGMTID09 VARCHAR(3),
	SGMTID10 VARCHAR(3),
	SGMTID11 VARCHAR(3),
	SGMTID12 VARCHAR(3),
	RDSIND01 VARCHAR(1),
	RDSIND02 VARCHAR(1),
	RDSIND03 VARCHAR(1),
	RDSIND04 VARCHAR(1),
	RDSIND05 VARCHAR(1),
	RDSIND06 VARCHAR(1),
	RDSIND07 VARCHAR(1),
	RDSIND08 VARCHAR(1),
	RDSIND09 VARCHAR(1),
	RDSIND10 VARCHAR(1),
	RDSIND11 VARCHAR(1),
	RDSIND12 VARCHAR(1),
	DUAL_01 VARCHAR(2),
	DUAL_02 VARCHAR(2),
	DUAL_03 VARCHAR(2),
	DUAL_04 VARCHAR(2),
	DUAL_05 VARCHAR(2),
	DUAL_06 VARCHAR(2),
	DUAL_07 VARCHAR(2),
	DUAL_08 VARCHAR(2),
	DUAL_09 VARCHAR(2),
	DUAL_10 VARCHAR(2),
	DUAL_11 VARCHAR(2),
	DUAL_12 VARCHAR(2),
	CSTSHR01 VARCHAR(2),
	CSTSHR02 VARCHAR(2),
	CSTSHR03 VARCHAR(2),
	CSTSHR04 VARCHAR(2),
	CSTSHR05 VARCHAR(2),
	CSTSHR06 VARCHAR(2),
	CSTSHR07 VARCHAR(2),
	CSTSHR08 VARCHAR(2),
	CSTSHR09 VARCHAR(2),
	CSTSHR10 VARCHAR(2),
	CSTSHR11 VARCHAR(2),
	CSTSHR12 VARCHAR(2),
	FILE VARCHAR(128),
	RECORD BIGSERIAL,
	STATE VARCHAR(2) GENERATED ALWAYS AS (STATE_CD) STORED,
	YEAR INT GENERATED ALWAYS AS (RFRNC_YR) STORED,
	ZIP VARCHAR(5) GENERATED ALWAYS AS (ZIP_CD) STORED,
	PRIMARY KEY (FILE, RECORD)
);
CREATE INDEX  mbsf_abcd_2017_FILE_idx ON cms.mbsf_abcd_2017 USING BTREE (FILE) ;
COMMENT ON table cms.mbsf_abcd_2017 IS 'CREATED BY Dorieh: {"version": "0.2.3", "url": "https://github.com/NSAPH-Data-Platform/dorieh", "commit": "bf4644403297aaf478f6a42717508dde55da409a"}. Created at 2024-08-12 13:31:09.333050';

---

Columns:

Column Name	Column Type	Datatype
a_mo_cnt		INT
a_trm_cd		VARCHAR(1)
age		INT
b_mo_cnt		INT
b_trm_cd		VARCHAR(1)
bene_dob		DATE
bene_id		VARCHAR(15)
buyin01		VARCHAR(1)
buyin02		VARCHAR(1)
buyin03		VARCHAR(1)
buyin04		VARCHAR(1)
buyin05		VARCHAR(1)
buyin06		VARCHAR(1)
buyin07		VARCHAR(1)
buyin08		VARCHAR(1)
buyin09		VARCHAR(1)
buyin10		VARCHAR(1)
buyin11		VARCHAR(1)
buyin12		VARCHAR(1)
buyin_mo		INT
cnty_cd		VARCHAR(3)
covstart		DATE
crec		VARCHAR(1)
crnt_bic		VARCHAR(2)
cstshr01		VARCHAR(2)
cstshr02		VARCHAR(2)
cstshr03		VARCHAR(2)
cstshr04		VARCHAR(2)
cstshr05		VARCHAR(2)
cstshr06		VARCHAR(2)
cstshr07		VARCHAR(2)
cstshr08		VARCHAR(2)
cstshr09		VARCHAR(2)
cstshr10		VARCHAR(2)
cstshr11		VARCHAR(2)
cstshr12		VARCHAR(2)
death_dt		DATE
dual_01		VARCHAR(2)
dual_02		VARCHAR(2)
dual_03		VARCHAR(2)
dual_04		VARCHAR(2)
dual_05		VARCHAR(2)
dual_06		VARCHAR(2)
dual_07		VARCHAR(2)
dual_08		VARCHAR(2)
dual_09		VARCHAR(2)
dual_10		VARCHAR(2)
dual_11		VARCHAR(2)
dual_12		VARCHAR(2)
dual_mo		INT
efivepct		VARCHAR(1)
enrl_src		VARCHAR(3)
esrd_ind		VARCHAR(1)
file	file	VARCHAR(128)
hmo_mo		INT
hmoind01		VARCHAR(1)
hmoind02		VARCHAR(1)
hmoind03		VARCHAR(1)
hmoind04		VARCHAR(1)
hmoind05		VARCHAR(1)
hmoind06		VARCHAR(1)
hmoind07		VARCHAR(1)
hmoind08		VARCHAR(1)
hmoind09		VARCHAR(1)
hmoind10		VARCHAR(1)
hmoind11		VARCHAR(1)
hmoind12		VARCHAR(1)
mdcr_stus_cd_01		VARCHAR(2)
mdcr_stus_cd_02		VARCHAR(2)
mdcr_stus_cd_03		VARCHAR(2)
mdcr_stus_cd_04		VARCHAR(2)
mdcr_stus_cd_05		VARCHAR(2)
mdcr_stus_cd_06		VARCHAR(2)
mdcr_stus_cd_07		VARCHAR(2)
mdcr_stus_cd_08		VARCHAR(2)
mdcr_stus_cd_09		VARCHAR(2)
mdcr_stus_cd_10		VARCHAR(2)
mdcr_stus_cd_11		VARCHAR(2)
mdcr_stus_cd_12		VARCHAR(2)
orec		VARCHAR(1)
ptc_cntrct_id_01		VARCHAR(5)
ptc_cntrct_id_02		VARCHAR(5)
ptc_cntrct_id_03		VARCHAR(5)
ptc_cntrct_id_04		VARCHAR(5)
ptc_cntrct_id_05		VARCHAR(5)
ptc_cntrct_id_06		VARCHAR(5)
ptc_cntrct_id_07		VARCHAR(5)
ptc_cntrct_id_08		VARCHAR(5)
ptc_cntrct_id_09		VARCHAR(5)
ptc_cntrct_id_10		VARCHAR(5)
ptc_cntrct_id_11		VARCHAR(5)
ptc_cntrct_id_12		VARCHAR(5)
ptc_pbp_id_01		VARCHAR(3)
ptc_pbp_id_02		VARCHAR(3)
ptc_pbp_id_03		VARCHAR(3)
ptc_pbp_id_04		VARCHAR(3)
ptc_pbp_id_05		VARCHAR(3)
ptc_pbp_id_06		VARCHAR(3)
ptc_pbp_id_07		VARCHAR(3)
ptc_pbp_id_08		VARCHAR(3)
ptc_pbp_id_09		VARCHAR(3)
ptc_pbp_id_10		VARCHAR(3)
ptc_pbp_id_11		VARCHAR(3)
ptc_pbp_id_12		VARCHAR(3)
ptc_plan_type_cd_01		VARCHAR(3)
ptc_plan_type_cd_02		VARCHAR(3)
ptc_plan_type_cd_03		VARCHAR(3)
ptc_plan_type_cd_04		VARCHAR(3)
ptc_plan_type_cd_05		VARCHAR(3)
ptc_plan_type_cd_06		VARCHAR(3)
ptc_plan_type_cd_07		VARCHAR(3)
ptc_plan_type_cd_08		VARCHAR(3)
ptc_plan_type_cd_09		VARCHAR(3)
ptc_plan_type_cd_10		VARCHAR(3)
ptc_plan_type_cd_11		VARCHAR(3)
ptc_plan_type_cd_12		VARCHAR(3)
ptd_mo		INT
ptdcntrct01		VARCHAR(5)
ptdcntrct02		VARCHAR(5)
ptdcntrct03		VARCHAR(5)
ptdcntrct04		VARCHAR(5)
ptdcntrct05		VARCHAR(5)
ptdcntrct06		VARCHAR(5)
ptdcntrct07		VARCHAR(5)
ptdcntrct08		VARCHAR(5)
ptdcntrct09		VARCHAR(5)
ptdcntrct10		VARCHAR(5)
ptdcntrct11		VARCHAR(5)
ptdcntrct12		VARCHAR(5)
ptdpbpid01		VARCHAR(3)
ptdpbpid02		VARCHAR(3)
ptdpbpid03		VARCHAR(3)
ptdpbpid04		VARCHAR(3)
ptdpbpid05		VARCHAR(3)
ptdpbpid06		VARCHAR(3)
ptdpbpid07		VARCHAR(3)
ptdpbpid08		VARCHAR(3)
ptdpbpid09		VARCHAR(3)
ptdpbpid10		VARCHAR(3)
ptdpbpid11		VARCHAR(3)
ptdpbpid12		VARCHAR(3)
race		VARCHAR(1)
rds_mo		INT
rdsind01		VARCHAR(1)
rdsind02		VARCHAR(1)
rdsind03		VARCHAR(1)
rdsind04		VARCHAR(1)
rdsind05		VARCHAR(1)
rdsind06		VARCHAR(1)
rdsind07		VARCHAR(1)
rdsind08		VARCHAR(1)
rdsind09		VARCHAR(1)
rdsind10		VARCHAR(1)
rdsind11		VARCHAR(1)
rdsind12		VARCHAR(1)
record		BIGSERIAL
rfrnc_yr		INT
rti_race_cd		VARCHAR(1)
sample_group		VARCHAR(2)
sex		VARCHAR(1)
sgmtid01		VARCHAR(3)
sgmtid02		VARCHAR(3)
sgmtid03		VARCHAR(3)
sgmtid04		VARCHAR(3)
sgmtid05		VARCHAR(3)
sgmtid06		VARCHAR(3)
sgmtid07		VARCHAR(3)
sgmtid08		VARCHAR(3)
sgmtid09		VARCHAR(3)
sgmtid10		VARCHAR(3)
sgmtid11		VARCHAR(3)
sgmtid12		VARCHAR(3)
state	generated	VARCHAR(2)
state_cd		VARCHAR(2)
state_cnty_fips_cd_01		VARCHAR(5)
state_cnty_fips_cd_02		VARCHAR(5)
state_cnty_fips_cd_03		VARCHAR(5)
state_cnty_fips_cd_04		VARCHAR(5)
state_cnty_fips_cd_05		VARCHAR(5)
state_cnty_fips_cd_06		VARCHAR(5)
state_cnty_fips_cd_07		VARCHAR(5)
state_cnty_fips_cd_08		VARCHAR(5)
state_cnty_fips_cd_09		VARCHAR(5)
state_cnty_fips_cd_10		VARCHAR(5)
state_cnty_fips_cd_11		VARCHAR(5)
state_cnty_fips_cd_12		VARCHAR(5)
v_dod_sw		VARCHAR(1)
year	generated	INT
zip	generated	VARCHAR(5)
zip_cd		VARCHAR(5)