1 / 20

NASTAD EMR Request

NASTAD EMR Request. Data reporting for the National Alliance of State & Territorial AIDS Directors By Claire Tucker. Initial request for data:. What questions should the report attempt to answer?. …continued. Initial request for data:. General summary of needs:

lundy
Download Presentation

NASTAD EMR Request

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. NASTAD EMR Request Data reporting for the National Alliance of State & Territorial AIDS Directors By Claire Tucker

  2. Initial request for data: What questions should the report attempt to answer?

  3. …continued

  4. Initial request for data: • General summary of needs: • Data from southern regions of Haiti only (Nippes, Grande-anse, Sud, Sud-est). • Ability to track individual patients or groups of patients over time. • Ability to track prevalence of particular symptoms, behaviors or other characteristics across the dataset. • Ability to compile aggregate data based on a given variable or combination of variables.

  5. First steps : • Map each requested data element to corresponding location/field on forms. • Map each field to its location in the database. • Send initial documentation to Genevive Meredith (Maine Center for Disease Control and Prevention), Chris Delcher and Carrie Dolan (Virginia Department of Health).

  6. Example of mapping data to database location:

  7. Challenges at first stages of processing: • Organizing and consolidating data request: • Many elements in data request were identical in terms of adult vs. pediatric or intake vs. follow-up. • Some elements were more difficult to map to their corresponding forms. For example, OI (opportunistic infection) diagnoses are not specified on any forms.

  8. Challenges at first stages of processing: • Determining needs of users: • All symptoms/stages, or only WHO-specified? • HIV symptoms by stage or by specific symptom?

  9. Next step: documenting possible values for each data element…

  10. Writing database procedure: basic structure… create proc sp_nastad as if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nastadTable]') and OBJECTPROPERTY(id, N'IsTable') = 1) drop table [dbo].[nastadTable] create table dbo.nastadTable (nid bigint identity, sitecode char(10), patientid varchar(11), visitdate smalldatetime null, dataElement int, value varchar(200) null); /* Facility Name */ insert into nastadTable (sitecode,patientid,visitdate, dataelement, value) select distinct s.sitecode, patientid, visitDate, 6, s.Site from v_patients p, siteName s where s.sitecode = p.sitecode and encounterType = 10 /* patient address */ insert into nastadTable (sitecode,patientid,visitdate, dataelement, value) select distinct sitecode, patientid, visitDate, 7, addrDistrict + ' ' + addrSection + addrTown from v_patients p where encounterType = 10 /* Mother's first name */ insert into nastadTable (sitecode,patientid,visitdate, dataelement, value) select distinct sitecode, patientid, visitDate, 11, fnameMother from v_patients where encounterType = 10 /* nationalid */ insert into nastadTable (sitecode,patientid,visitdate, dataelement, value) select distinct sitecode, patientid, visitDate, 12, nationalid from v_patients where encounterType = 10

  11. Many queries were written with case statements to facilitate analysis: /* HIV symptoms by stages - adult */ insert into nastadTable select distinct sitecode, patientid, visitdate, 21, case when asymptomaticWHO = 1 then 'asymptomaticWHO' when weightLossLessTenPercMo = 1 then 'weightLossLessTenPercMo' when weightLossPlusTenPercMo = 1 then 'weightLossPlusTenPercMo' when diarrheaLessMo = 1 then 'diarrheaLessMo' when diarrheaPlusMo = 1 then 'diarrheaPlusMo' when chronicWeakness = 1 then 'chronicWeakness' when feverLessMo = 1 then 'feverLessMo' when feverPlusMo = 1 then 'feverPlusMo' when wtLossTenPercWithDiarrMo = 1 then 'wtLossTenPercWithDiarrMo' when abpain = 1 then 'abpain' when anorexia = 1 then 'anorexia' when cough = 1 then 'cough‘…

  12. Next step: creating NASTAD table and NASTAD lookup table… Sample data from NastadTable:

  13. Next step: creating NASTAD table and NASTAD lookup table… Sample data from NastadLookup table:

  14. Using the tables together: • Example query: “select top 5 sitecode, patientid, visitdate, n.dataelement, fieldname, descriptionEnglish, descriptionFrench, value from nastadTable n, nastadLookup l where n.dataelement = l.dataelement and l.dataelement = 7” • Results:

  15. Next steps: transmitting the data to NASTAD people… • Tried sending an Excel file, which we generated using the Enterprise Manager, by creating a view based upon nastadTable and nastadLookup ... However, the resulting dataset was too large for an Excel table so we created a text file containing the data. Sample of the resulting text file: "sitecode","nationalID","dataElement","fieldname","value","Visitdate" "21100 ","",1,"nationalID"," ",2001-01-01 00:00:00 "21100 "," VL0460D",1,"nationalID"," VL0460D",2001-01-01 00:00:00 "21100 ","AB0781Y",1,"nationalID","AB0781Y",2001-01-01 00:00:00 "21100 ","AB0781Y",2,"address","Rue du Port ( Zone ti Mouillage ) Jacmel Cayes Jacmel", 2007-05-30 00:00:00 "21100 ","AB0781Y",3,"fnameMother","Yline",2007-05-30 00:00:00 "21100 ","AB0781Y",4,"birthPlace","Marigot ",2007-05-30 00:00:00 "21100 ","AB0781Y",5,"site","Hopital St Michel ",2007-05-30 00:00:00

  16. Transmitting the data to NASTAD people (continued) … • It was necessary to run the dbproc and create the tables and view in the consolidated database on sapote (itechUWfailover) since many of the southern department clinics are now running in-country. • Text file was placed on NASTAD sftp location for download.

  17. Sample data from view v_nastad:

  18. Wrapping things up… • Fielding questions from the requestors and performing necessary modifications to the result set. • Missing data: there were some concerns about the completeness of the dataset. This was due to many fields being left blank on patient forms. • Minor errors in dataset; for example, we misspelled “Grande-anse” so data for that region was left out initially. The error was noted by NASTAD researchers and we provided modified files for them. • Providing supplementary material (Access file containing information on localities in each region) to assist in analysis.

  19. Suggestions for simplifying execution of future data requests: • Create a lookup table for all data-elements, mapping them to their corresponding forms, labels, and database locations. • Also include French labels/descriptions. • Improving data quality will improve results of data requests and reduce incidence of null result sets.

  20. In summary: a framework for future data requests… • Map form elements to their database locations. • Normalize the data; ensure all data is represented in a uniform format. Use of views is helpful. • Generate a result dataset. Queries for individual data variables may be written against a master table.

More Related