search

PHSKC-APDE / claims_data

Process and analyze WA State Medicaid, Medicare, and All-Payer Claims Database eligibility and claims data
21 stars 4 forks source link

Updating claim_header, claim_ccw and claim_bh tables #169

Closed elikern-kc closed 11 months ago

elikern-kc commented 1 year ago

Changes to make to [mcaid_claim_header] table

  1. Add new columns using new flags in ref.icdcm_codes: a. mh_primary b. mh_any c. sud_primary d. sud_any
  2. Add other new columns: a. ed_pophealth (0/1 flag) b. ccs_broad_desc c. ccs_broad_code d. ccs_detail_desc e. ccs_detail_code f. injury_nature_narrow g. injury_nature_broad h. injury_nature_type i. injury_nature_icdcm j. injury_ecode
  3. Rename columns: a. ed -> ed_perform b. intent -> injury_intent c. mechanism -> injury_mechanism
  4. Remove columns: a. mental_dx1 b. mental_dxany c. mental_dx_rda_any d. sud_dx_rda_any e. maternal_dx1 f. maternal_broad_dx1 g. newborn_dx1 h. ed i. ed_nohosp j. ed_bh k. ed_avoid_ca l. ed_avoid_ca_nohosp m. ed_ne_nyu n. ed_pct_nyu o. ed_pa_nyu p. ed_npa_nyu q. ed_mh_nyu r. ed_sud_nyu s. ed_alc_nyu t. ed_injury_nyu u. ed_unclass_nyu v. ed_emegent_nyu w. ed_nonemergent_nyu x. ed_intermediate_nyu y. ipt_medsurg z. ipt_bh aa. sdoh_any bb. ed_sdoh cc. ipt_sdoh dd. ccs – ccs_final_plain_language
  5. Make changes to YAML file also!!!
  6. Run by writing table to claims schema using a tmp prefix a. YAML file : to_table -> change back to stage_mcaid_claim_header AFTER TESTING
  7. Then push script to GitHub with pull request for Jeremy

Changes to make to [mcaid_claim_ccw] table:

  1. Update YAML file to reference new ref.icdcm_codes table -> change ref schema and ref table prefix (to blank)
  2. Update YAML file for WA-APCD data too to add schema and table name for ref.icdcm_codes
  3. Update R script to update hard-coded name for reference table (dx_lookup)
  4. Then push script to GitHub with pull request for Jeremy

Changes to make to [mcaid_claim_bh] table:

  1. Update YAML file to reference new RDA value sets reference table
  2. Update R script to align with new RDA value set reference table
  3. Then push script to GitHub with pull request for Jeremy
alastairmatheson commented 12 months ago

I looked over the changes in moderate detail and things looked good to me. Is there a better way to identify maternal and newborn-related claims now that maternal_dx1, maternal_broad_dx1, and newborn_dx1 have been dropped?

elikern-kc commented 12 months ago

Thanks Alastair! Once I'm done with the parent-child health exploratory work I'm doing, I think we will have better flags that could either i) be added back to the claim header table or ii) stored in a new PCH-themed table (more likely I think).

Best, Eli

Eli Kern MPH RN | Epidemiologist (he/him) Assessment, Policy Development, and Evaluation Public Health - Seattle and King Countyhttps://kingcounty.gov/depts/health.aspx phone: 206.263.8727 | email: @.***https://owa.kingcounty.gov/owa/redir.aspx?C=TNuruUR_x0mMeOqn6rueKCfnLzCcxdAICR3iDfSS8atu12V25XSLIp_1k_j_A9NKOb-a_2dkPVw.&URL=mailto%3aeli.kern%40kingcounty.gov

From: Alastair Matheson @.> Sent: Monday, September 18, 2023 12:56 PM To: PHSKC-APDE/claims_data @.> Cc: Kern, Eli @.>; Author @.> Subject: Re: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

[EXTERNAL Email Notice! ] External communication is important to us. Be cautious of phishing attempts. Do not click or open suspicious links or attachments.

I looked over the changes in moderate detail and things looked good to me. Is there a better way to identify maternal and newborn-related claims now that maternal_dx1, maternal_broad_dx1, and newborn_dx1 have been dropped?

- Reply to this email directly, view it on GitHubhttps://github.com/PHSKC-APDE/claims_data/pull/169#issuecomment-1724278847, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABL6ZKWOAT6Y2RKSGJITLF3X3CRNBANCNFSM6AAAAAA42H4PMU. You are receiving this because you authored the thread.Message ID: @.***>

elikern-kc commented 11 months ago

Thank you Kai!

Jeremy - I think your review and approval is the last step before merging these changes into the main branch, and then rerunning the Medicaid analytic tables.

Thank you!

Eli

Eli Kern MPH RN | Epidemiologist (he/him) Assessment, Policy Development, and Evaluation Public Health - Seattle and King Countyhttps://kingcounty.gov/depts/health.aspx phone: 206.263.8727 | email: @.***https://owa.kingcounty.gov/owa/redir.aspx?C=TNuruUR_x0mMeOqn6rueKCfnLzCcxdAICR3iDfSS8atu12V25XSLIp_1k_j_A9NKOb-a_2dkPVw.&URL=mailto%3aeli.kern%40kingcounty.gov

From: Kai Fukutaki @.> Sent: Thursday, September 21, 2023 11:30 AM To: PHSKC-APDE/claims_data @.> Cc: Kern, Eli @.>; Author @.> Subject: Re: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

[EXTERNAL Email Notice! ] External communication is important to us. Be cautious of phishing attempts. Do not click or open suspicious links or attachments.

@kai-fukutaki approved this pull request.

Only saw a moderate nit on it probably being better to use '=' rather than 'in' for when it's length one, but not worth changing. Wasn't super sure what to look for when vetting yaml files but they seemed alright.

- Reply to this email directly, view it on GitHubhttps://github.com/PHSKC-APDE/claims_data/pull/169#pullrequestreview-1638475038, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABL6ZKRJCVSYE6AE3GFKSX3X3SBUBANCNFSM6AAAAAA42H4PMU. You are receiving this because you authored the thread.Message ID: @.***>

elikern-kc commented 11 months ago

I will look it over and pull it in. I'm currently consolidating the latest raw mcaid files that became available this morning. Once that is done, I'll be able to run it all.

Jeremy

From: Kern, Eli @.> Sent: Thursday, September 21, 2023 11:36 AM To: PHSKC-APDE/claims_data @.>; PHSKC-APDE/claims_data @.> Cc: Author @.>; Whitehurst, Jeremy @.***> Subject: RE: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

Thank you Kai!

Jeremy - I think your review and approval is the last step before merging these changes into the main branch, and then rerunning the Medicaid analytic tables.

Thank you! Eli

Eli Kern MPH RN | Epidemiologist (he/him) Assessment, Policy Development, and Evaluation Public Health - Seattle and King Countyhttps://kingcounty.gov/depts/health.aspx phone: 206.263.8727 | email: @.***https://owa.kingcounty.gov/owa/redir.aspx?C=TNuruUR_x0mMeOqn6rueKCfnLzCcxdAICR3iDfSS8atu12V25XSLIp_1k_j_A9NKOb-a_2dkPVw.&URL=mailto%3aeli.kern%40kingcounty.gov

From: Kai Fukutaki @.**@.>> Sent: Thursday, September 21, 2023 11:30 AM To: PHSKC-APDE/claims_data @.**@.>> Cc: Kern, Eli @.**@.>>; Author @.**@.>> Subject: Re: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

[EXTERNAL Email Notice! ] External communication is important to us. Be cautious of phishing attempts. Do not click or open suspicious links or attachments.

@kai-fukutaki approved this pull request.

Only saw a moderate nit on it probably being better to use '=' rather than 'in' for when it's length one, but not worth changing. Wasn't super sure what to look for when vetting yaml files but they seemed alright.

- Reply to this email directly, view it on GitHubhttps://github.com/PHSKC-APDE/claims_data/pull/169#pullrequestreview-1638475038, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABL6ZKRJCVSYE6AE3GFKSX3X3SBUBANCNFSM6AAAAAA42H4PMU. You are receiving this because you authored the thread.Message ID: @.**@.>>

elikern-kc commented 11 months ago

Awesome – thank you so much Jeremy! Remember to pull me in if you encounter any errors given that I feel responsible 😊

Eli

Eli Kern MPH RN | Epidemiologist (he/him) Assessment, Policy Development, and Evaluation Public Health - Seattle and King Countyhttps://kingcounty.gov/depts/health.aspx phone: 206.263.8727 | email: @.***https://owa.kingcounty.gov/owa/redir.aspx?C=TNuruUR_x0mMeOqn6rueKCfnLzCcxdAICR3iDfSS8atu12V25XSLIp_1k_j_A9NKOb-a_2dkPVw.&URL=mailto%3aeli.kern%40kingcounty.gov

From: Whitehurst, Jeremy @.> Sent: Thursday, September 21, 2023 11:38 AM To: Kern, Eli @.>; PHSKC-APDE/claims_data @.>; PHSKC-APDE/claims_data @.> Cc: Author @.***> Subject: RE: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

I will look it over and pull it in. I’m currently consolidating the latest raw mcaid files that became available this morning. Once that is done, I’ll be able to run it all.

Jeremy

From: Kern, Eli @.**@.>> Sent: Thursday, September 21, 2023 11:36 AM To: PHSKC-APDE/claims_data @.**@.>>; PHSKC-APDE/claims_data @.**@.>> Cc: Author @.**@.>>; Whitehurst, Jeremy @.**@.>> Subject: RE: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

Thank you Kai!

Jeremy – I think your review and approval is the last step before merging these changes into the main branch, and then rerunning the Medicaid analytic tables.

Thank you! Eli

Eli Kern MPH RN | Epidemiologist (he/him) Assessment, Policy Development, and Evaluation Public Health - Seattle and King Countyhttps://kingcounty.gov/depts/health.aspx phone: 206.263.8727 | email: @.***https://owa.kingcounty.gov/owa/redir.aspx?C=TNuruUR_x0mMeOqn6rueKCfnLzCcxdAICR3iDfSS8atu12V25XSLIp_1k_j_A9NKOb-a_2dkPVw.&URL=mailto%3aeli.kern%40kingcounty.gov

From: Kai Fukutaki @.**@.>> Sent: Thursday, September 21, 2023 11:30 AM To: PHSKC-APDE/claims_data @.**@.>> Cc: Kern, Eli @.**@.>>; Author @.**@.>> Subject: Re: [PHSKC-APDE/claims_data] Updating claim_header, claim_ccw and claim_bh tables (PR #169)

[EXTERNAL Email Notice! ] External communication is important to us. Be cautious of phishing attempts. Do not click or open suspicious links or attachments.

@kai-fukutaki approved this pull request.

Only saw a moderate nit on it probably being better to use '=' rather than 'in' for when it's length one, but not worth changing. Wasn't super sure what to look for when vetting yaml files but they seemed alright.

— Reply to this email directly, view it on GitHubhttps://github.com/PHSKC-APDE/claims_data/pull/169#pullrequestreview-1638475038, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABL6ZKRJCVSYE6AE3GFKSX3X3SBUBANCNFSM6AAAAAA42H4PMU. You are receiving this because you authored the thread.Message ID: @.**@.>>