Using a Lookup Table to Transform Electronic Laboratory Results into Standardized

1. Using a Lookup Table to Transform Electronic Laboratory Results into Standardized Case-Specific Information to Manage a Record-Breaking Flu Season September 5-7, 2018Presenting To: WUSS Conference | Sacramento, CA Rachel Perry, MSc, MLS(ASCP)CM | Cross-Cutting Epidemiologist

2. Using a Lookup Table to Transform Electronic Laboratory Results into Standardized Case-Specific Information to Manage a Record-Breaking Flu Season Rachel Perry, MSc, MLS(ASCP)CM | Cross-Cutting Epidemiologist Rachel is a Cross-Cutting Epidemiologist with the Arizona Department of Health Services. She performs general infectious disease surveillance activities and assists on various projects, such as evaluating laboratory data for influenza surveillance. Prior to this, she received her Masters of Science in Epidemiology and Biostatistics from the University of Leeds and worked at Carolinas Healthcare System in Charlotte, NC as a medical laboratory scientist.

5. The 2017-2018 season had over 35,000 cases, nearly 20,000 more than the 5-season average.

8. Test performed Q Code Test code Free text Test result Q Code Test code Free text

9. TheGoal

11. Lookup Table Basics Primary Data Set Lookup Table New Data Set

12. Developing the Lookup Table

13. Developing the Lookup Table datalookup_table; setlookup_table;final=catx(" ", test_name, test_result); run;

14. Developing the Lookup Table Test performed Test result

15. Developing the Lookup Table Test performed Test result

16. Developing the Lookup Table Test performed Test result

17. Developing the Lookup Table Test performed Test result

18. Developing the Lookup Table Test performed Test result

19. Developing the Lookup Table Test performed Test result

20. Developing the Lookup Table – Additional Information

21. Developing the Lookup Table – Additional Information

23. 2

24. 2 A/H3

25. Developing the Lookup Table – Additional Information dataprimary_data_set; setprimary_data_set; iftest_name="34487-9 INFLUENZA VIRUS A RNA (INFLUENZA A)" andtest_result in ("10828004 POSITIVE SCT P POSITIVE", "G-A200 POSITIVE") thendo; ifINDEX(LABNOTES, "SUBTYPE: H3")>0then flag="H3"; end; run;

26. Developing the Lookup Table – Additional Information

27. Developing the Lookup Table – Additional Information dataprimary_data_set; setprimary_data_set; final=catx(" ", test_name, test_result, flag); run; datalookup_table; setlookup_table; final=catx(" ", test_name, test_result, flag); run;

28. The Lookup Table Test result • Flag Q Code 1 Test code 1 Free text 1 Test performed Q Code Test code Free text

29. Combining the Lookup Table with the Primary Data Set

30. Combining the Lookup Table with the Primary Data Set Lookup Table

31. Combining the Lookup Table with the Primary Data Set Lookup Table

32. Combining the Lookup Table with the Primary Data Set Lookup Table Primary Data Set

33. Combining the Lookup Table with the Primary Data Set Lookup Table Primary Data Set

34. Combining the Lookup Table with the Primary Data Set Lookup Table Primary Data Set Key variable: final=catx(" ", test_name, test_result, flag);

35. Combining the Lookup Table with the Primary Data Set New Data Set

36. Combining the Lookup Table with the Primary Data Set procsql; createtablenew_data_setas selecta.medsisid, b.qcode, b.test_code, b.free_text, b.qcode1, b.test_code1, b.free_text1, b.final fromprimary_data_set a leftjoinlookup_table b on (a.final=b.final) orderbymedsisid; quit; New Data Set

38. But….

39. Determining if your Lookup Table is Up-To-Date

40. Determining if your Lookup Table is Up-To-Date procfreqdata= primary_data_set; tablestest_name*test_result / out=ELR_tests;whereELR="YES"; run;

41. Determining if your Lookup Table is Up-To-Date procfreqdata= primary_data_set; tablestest_name*test_result / out=ELR_tests;whereELR="YES"; run; dataELR_tests (keep=test_nametest_result match); setELR_tests; match=catx("", test_name, test_result); run;

42. Determining if your Lookup Table is Up-To-Date procfreqdata= primary_data_set; tablestest_name*test_result / out=ELR_tests;whereELR="YES"; run; dataELR_tests (keep=test_nametest_result match); setELR_tests; match=catx("", test_name, test_result); run; data old_tests (keep=test_nametest_result match); setlookup_table; match=catx("", test_name, test_result); run;

43. Determining if your Lookup Table is Up-To-Date procfreqdata= primary_data_set; tablestest_name*test_result / out=ELR_tests;whereELR="YES"; run; dataELR_tests (keep=test_nametest_result match); setELR_tests; match=catx("", test_name, test_result); run; data old_tests (keep=test_nametest_result match); setlookup_table; match=catx("", test_name, test_result); run; procsortdata=ELR_tests; by match; run; procsortdata=old_tests; by match; run;

44. Determining if your Lookup Table is Up-To-Date procfreqdata= primary_data_set; tablestest_name*test_result / out=ELR_tests;whereELR="YES"; run; dataELR_tests (keep=test_nametest_result match); setELR_tests; match=catx("", test_name, test_result); run; data old_tests (keep=test_nametest_result match); setlookup_table; match=catx("", test_name, test_result); run; procsortdata=ELR_tests; by match; run; procsortdata=old_tests; by match; run; datanew_tests; mergeELR_tests (in=a) old_tests (in=b); bymatch; ifa and not b; run;

46. Thank You Rachel Perry | Cross-Cutting Epidemiologist Rachel.Perry@azdhs.gov | 602-364-4068 azhealth.gov @azdhs facebook.com/azdhs