Statistical Process Improvement
Georgetown University
 

Preparing Data


 

Assigned Reading

  1. Introduction to Preparing Data (use instructor's last name as password) Read►
  2. Tables and records using Access  More►
  3. Queries using Access More►

Presentations

Narrated slides and videos require Flash. Download►

Assignment

To complete these assignments, you need to download either Microsoft SQL Server or Microsoft Access.  Video►  

  • MAC users should first download and install virtualization software (VirtualBox, VMWare Player/Fusion, Parallels). Get a copy of an operating system from MSDN (i.e. Windows 10), or Linux (i.e., Ubuntu, or CentOS). Configure a virtual machine and install the guest operating system. Note: In this course we will be mostly using VirtualBox, but you can use any virtualization solution. 
  • Install MS SQL.  DreamSpark (with Microsoft software) library is available free of charge for GMU students. Click on start shopping.  Select SQL Server 2014 Express With Advanced Services With Service Pack 1 32/64-bit (English). Download►  
  • Use your GMU email to register (without Masonlive, i.e. userid@gmu.edu). Do not use your GMU password. Only Microsoft software is available on this site. Software is FREE. Contact instructor if your password does not work.

Question 1: Import data from the following four files into four tables.  Ptid file► Claims file► ICD file► CPT file► Video►

  • Identify patients that have diabetes in the above database.  Video►
  • Calculate the average cost of each diagnosis sorted from most expensive to least expensive.  Exclude all bills with negative or 0 values.  Video►
  • Show if men are more likely to have diabetes than women.  Video►
  • Calculate which month is most likely to have a diagnosis reported.  Video►

Question 2: Download the attached file of ICD9 codes and descriptions and find the seven errors in the data, where the same ICD9 code has been assigned different descriptions.  Data► Video►

Question 3: Download data in three/four zipped files.You can focus on the entire data or focus on the data for patients who have at least 365 days of encounters. For password contact your instructor.  By opening this file you agree not to share the file with anyone else.  Unzip the files twice, first to get to the directory and then to get to the actual file.  Link to the data into Access or read the data into Microsoft SQL server.  We recommend you use SQL server.  To create the database, open SQL server and right click on the database and start a new database.  Right click on the database, select "Tasks," select "Import Data," select "Flat File Source" as the source of the data, change file type to "CSV Files," browse to where you unzipped the files, indicate that field names are in the first row, select as destination "SQL Server Native Client" file type. Massive Data► >365 Days Data, Access Code► SQL Code to Merge Files► Create Database► Visual Guide to Read SQL►

  • Remove blanks from numeric data, such as DxAtAge.  Convert text data in the AgeAtDeath to float.  This is typically done through if statements such as these:
    •  IIF(DxAtAge >0, DxAtAge, Null)
    • IIF(AgeAtDeath="Null", Null, cast(AgeAtDeath AS Float))
  • Calculate the average age of and the standard deviation of the diagnosis.  Which 10 diagnoses occur first, meaning which diagnosis occurs at a younger age.   Access Code► SQL Code & Answer►
  • List the top 20 most frequent diagnoses that co-occur.  To complete this task you would need to join the table to itself.  Then, use ICD9 code in one table as the first and the ICD9 code in the second table as the second of the pair.  Count the number that match any pair of diagnoses.  Access Code► SQL Code►
  • Use STUFF function to concatenate list of unique diagnoses for the same person.  Count numbers of times these lists occur more than 29 times STUFF SQL►
  • Identify individuals whose date of death might be in error and have visits post date of death. Exclude them. Report the top 10 IDs that remain in order of IDs.  Access Code► SQL Code►
  • Rank order diagnosis in order of their reoccurrence for the same person.  Rank or Row Number functions are described on the web.  Search for these commands and look at some examples online.  For how many unique persons does the ICD diagnoses 250.00 repeat twice in the data? Rank Command►

    For example, the following tables shows how the rank order should work for person with ID 1:

ID ICD9 Rank  
1 410 1 First occurrence of diagnosis 410
1 250 1 First occurrence of diagnosis 250
1 410 2 Second occurrence of diagnosis 410
1 250 2 Second occurrence of diagnosis 250
1 250 3  
1 100 1  
1 250 4  

More

 

 
Copyright 1996 Farrokh Alemi, Ph.D. Most recent revision 09/10/2017.  This page is part of the course on Statistical Process Improvement, this is the lecture on Preparing Data