documentation:vocabulary_etl
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
|
documentation:vocabulary_etl [2015/12/10 12:57] hardhouse |
documentation:vocabulary_etl [2016/03/29 12:49] hardhouse add ICD10CM |
||
|---|---|---|---|
| Line 69: | Line 69: | ||
| | SNOMED | UK HSCIC Website | https://isd.hscic.gov.uk/trud3/user/authenticated/group/0/pack/6/subpack/25/releases | https://isd.hscic.gov.uk/artefact/trud3/1mebfzrdolgi1kwge9xh0ddgtl/NHSBSA/6.3.0/NHSBSA_DMDBONUS/nhsbsa_dmdbonus_6.3.0_20150622000001.zip | | | SNOMED | UK HSCIC Website | https://isd.hscic.gov.uk/trud3/user/authenticated/group/0/pack/6/subpack/25/releases | https://isd.hscic.gov.uk/artefact/trud3/1mebfzrdolgi1kwge9xh0ddgtl/NHSBSA/6.3.0/NHSBSA_DMDBONUS/nhsbsa_dmdbonus_6.3.0_20150622000001.zip | | ||
| | RxNorm, NDFRT, VA Product, VA Class, ATC, MeSH, ICD10, GCN_SEQNO, ETC, Indication | UMLS website | http://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormfiles.html | http://download.nlm.nih.gov/umls/kss/rxnorm/RxNorm_full_03022015.zip | | | RxNorm, NDFRT, VA Product, VA Class, ATC, MeSH, ICD10, GCN_SEQNO, ETC, Indication | UMLS website | http://www.nlm.nih.gov/research/umls/rxnorm/docs/rxnormfiles.html | http://download.nlm.nih.gov/umls/kss/rxnorm/RxNorm_full_03022015.zip | | ||
| - | | HCPCS| CMS website | http://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/Alpha-Numeric-HCPCS.html | http://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/Downloads/2015-Annual-Alpha-Numeric-HCPCS-File.zip | | + | | HCPCS| CMS website | http://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/Alpha-Numeric-HCPCS.html | https://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/Downloads/2016-Alpha-Numeric-HCPCS-File.zip | |
| | ICD9CM and ICD9Proc | CMS website | http://www.cms.gov/Medicare/Coding/ICD9ProviderDiagnosticCodes/codes.html| http://www.cms.gov/Medicare/Coding/ICD9ProviderDiagnosticCodes/Downloads/ICD-9-CM-v32-master-descriptions.zip | | | ICD9CM and ICD9Proc | CMS website | http://www.cms.gov/Medicare/Coding/ICD9ProviderDiagnosticCodes/codes.html| http://www.cms.gov/Medicare/Coding/ICD9ProviderDiagnosticCodes/Downloads/ICD-9-CM-v32-master-descriptions.zip | | ||
| + | | ICD10CM | CDC website | http://www.cdc.gov/nchs/icd/icd10cm.htm | ftp://ftp.cdc.gov/pub/Health_Statistics/NCHS/Publications/ICD10CM/20xx/ICD10CM_FY20xx_code_descriptions.zip | | ||
| | LOINC| LOIC website | https://loinc.org/downloads/files/loinc-table-csv-text-format| http://loinc.org/downloads/files/loinc-table-csv-text-format/loinc-table-file-csv-text-format/download | | | LOINC| LOIC website | https://loinc.org/downloads/files/loinc-table-csv-text-format| http://loinc.org/downloads/files/loinc-table-csv-text-format/loinc-table-file-csv-text-format/download | | ||
| | LOINC| LOIC website | https://loinc.org/downloads/files/loinc-multiaxial-hierarchy| http://loinc.org/downloads/files/loinc-multiaxial-hierarchy/loinc-multiaxial-hierarchy-file/download | | | LOINC| LOIC website | https://loinc.org/downloads/files/loinc-multiaxial-hierarchy| http://loinc.org/downloads/files/loinc-multiaxial-hierarchy/loinc-multiaxial-hierarchy-file/download | | ||
| Line 104: | Line 105: | ||
| * ICD10 | * ICD10 | ||
| * ICD9CM | * ICD9CM | ||
| + | * ICD10CM | ||
| * ICD9Proc | * ICD9Proc | ||
| * LOINC | * LOINC | ||
| Line 303: | Line 305: | ||
| - Run create_source_tables.sql | - Run create_source_tables.sql | ||
| - | - Download the latest file http://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/Alpha-Numeric-HCPCS.html, file name YYYY-Annual-Alpha-Numeric-HCPCS-File.zip | + | - Download the latest file http://www.cms.gov/Medicare/Coding/HCPCSReleaseCodeSets/Alpha-Numeric-HCPCS.html, file name YYYY-Alpha-Numeric-HCPCS-File.zip |
| - | - Extract HCPCYYYY_CONTR_ANWEB_v2.xlsx | + | - Extract HCPCYYYY_CONTR_ANWEB.xlsx |
| - | - Open the above file in excel and export a csv file (with double quoted fields) called HCPC2015_CONTR_ANWEB_v2.csv for loading into ANWEB_V2 staging table. | + | - Open the above file in excel and export a csv file (with double quoted fields) called ANWEB_V2.csv for loading into ANWEB_V2 staging table. |
| - Load the file data into ANWEB_V2. Use the control files of the same name. | - Load the file data into ANWEB_V2. Use the control files of the same name. | ||
| - Run load_stage.sql | - Run load_stage.sql | ||
| Line 616: | Line 618: | ||
| - Run load_stage.sql | - Run load_stage.sql | ||
| - Run generic_update.sql (from working directory) | - Run generic_update.sql (from working directory) | ||
| - | |||
| ==== Step 16 ==== | ==== Step 16 ==== | ||
| Line 631: | Line 632: | ||
| - Run load_stage.sql | - Run load_stage.sql | ||
| - Run generic_update.sql (from working directory) | - Run generic_update.sql (from working directory) | ||
| + | |||
| + | |||
| + | |||
| ==== Step 17 ==== | ==== Step 17 ==== | ||
| + | |||
| + | === Overview === | ||
| + | |||
| + | Download the ICD10CM datasets. Decompress the gzipped files. Run the ETL script to create the staging tables schema and load the datasets. | ||
| + | |||
| + | === Details === | ||
| + | |||
| + | - Run create_source_tables.sql | ||
| + | - Download ftp://ftp.cdc.gov/pub/Health_Statistics/NCHS/Publications/ICD10CM/20xx/ICD10CM_FY20xx_code_descriptions.zip | ||
| + | - Extract icd10cm_order_20xx.txt and rename to icd10cm.txt | ||
| + | - Load into icd10cm_table with icd10cm.ctl | ||
| + | - Unzip icd10cm2snomed.zip and load into icd10cm2snomed with icd10cm2snomed.ctl | ||
| + | - Run load_stage.sql | ||
| + | - Run generic_update.sql (from working directory) | ||
| + | |||
| + | |||
| + | ==== Step 18 ==== | ||
| === Overview === | === Overview === | ||
| Line 643: | Line 664: | ||
| - Run drug_strength.sql | - Run drug_strength.sql | ||
| - | ==== Step 18 ==== | + | ==== Step 19 ==== |
| === Overview === | === Overview === | ||
| Line 656: | Line 677: | ||
| - Run manual_changes_15-Mar-2015.sql | - Run manual_changes_15-Mar-2015.sql | ||
| - | ==== Step 19 ==== | + | ==== Step 20 ==== |
| === Overview === | === Overview === | ||
documentation/vocabulary_etl.txt · Last modified: 2022/02/23 21:38 by mkallfelz
Page Tools
