Automating Rasch Based Survey Analysis:
This weblog will be designed to explain how we store patient survey data as well as explain how we have automated many of the steps of the analysis. It will be very technical in nature with the thought that the treasure is in the details and available to those that can recognize it…..meaning, that if I generalize much of this it won’t do anyone any good and by keeping it technical it will be very useful to those that may have the skills to implement some of the ideas and learnings we have had. With the understanding that all of my contact information is available and that I am always very happy to explain further.
Initially, it would take us almost 2 hours to take a patient survey (Activated Pt, Depression, or Physical Function) and analyze it into a useful result to be used by the Clinical Care Specialist. It did not matter if there were 2 or 200 surveys, it still took 2 hours to run through the multiple step process, ie storing the data, cleaning the data so it could be brought into Winsteps, exporting the data into SPSS so it could be graphically displayed, and then storing the data for use at a later date.
After learning and understanding the reporting needs, data structure requirements, and limitations of each application we were using for this process: Excel, MS Access, Winsteps, MS Word, SPSS, and Wordpad we were able to lay the groundwork to automate many of the steps. It is important to note that it is our expectation that with each step of automation we will not only save ourselves time but we will make the process easier to understand and less prone to user error. Each step of automation will decrease the likelihood that a mistake can be made.
The OVERVIEW
Once the surveys are complete they are handed into the P2 staff and their responses are key-entered into a Microsoft Access supported key-entry form specific to each survey. Once a week we (Data Analysts of P2) run a MS Access macro that extracts the results for each survey and saves it off to a text file. This text file is then loaded into Winsteps which is the application that performs the Rasch analysis of the data. Once Winsteps crunches the data it automatically writes off a text file. Another MS Access macro is run that imports the patient scores back into the database. These scores are on a 0-100 scale. The macro also produces a report listing only those scores that have come into the database since the last time the analysis was run.
The FIRST Step:
The first step was to set up the MS Access database, which we have been using to store the results, to handle a key-entry form specific to each survey. The form would allow the surveys to be key-entered directly into the database. The results had previously been key-entered into an Excel spreadsheet. We have the functionality to use the Teleform technology but an efficient process could not be set up to utilize it. The advantage of a MS Access based key-entry form is that it is easier to read for the end user and it has the ability to have better control over data quality. The forms can be set up to not allow duplicate entries, tight controls can be placed on the type of data entered into each field as well as not allowing fields to be left blank. The user is not required to save the file during or after entry. Further more, the volume of surveys is fairly small currently <10 per week. A short cut is placed on the user desktop that takes them directly into the MS Access dB menu screen that allows them to choose which form to key-enter.
The SECOND Step:
Next, functionality had to be put into place within the dB structure to allow us to identify patients and all of the surveys they took. Within the dB is survey data that may not be specific to P2, meaning that there is survey data from the large groups of folks that were used to test and calibrate the instruments. The functionality put into place allows each survey completed to have a unique id as well as to be labeled as a to whether or not it is a P2 patient. The database is also set up so that by using a patients PeaceHealth medical record number we can link a single patient to every survey they ever took.
The THIRD Step: Looking for new surveys
A 6 step macro is run that looks for new surveys, ie surveys that have been key-entered but not analyzed. This macro takes ~10sec to run.
Step 1 of the macro is to change the MS Access default so that all program warnings are turned off. A query that comes later in the macro is a MakeTable query which when run will produce various program warnings asking the user to confirm that they want to make a new table, etc. This first step just turn these warnings off which allows the macro to run in its entirety with out any further input by the end-user.
Step 2 is a MakeTable query that identifies all new Activated Pt surveys. This first query is a MakeTable query while the queries in steps 3&4 are append queries. The reasoning behind this is as Step 2 is run it is actually deleting the table currently there, which contains the listing of unique ids from the last time this macro was run. By replacing the table this MakeTable query is able to list just the unique id of the new surveys.
The structure of this query is parallel with the structure of the queries in steps 3 & 4. The table holding all of the key-entered surveys for the Activated Pt survey is joined to the Crosswalk table in a LEFT OUTER JOIN. The unique id filed is brought into each query. (The unique id field in the CrossWalk table is updated much later, during the export process. Therefore if the survey is new it will not have a unique id in the CrossWalk table.) A criterion is placed on the unique id field coming from the CrossWalk table to only bring back results that are NULL. The effect of this is that a new survey is identified IF it has a unique id in the key-enter table but not in the CrossWalk table.
Step 3 & 4 are append queries that identify new Depression and Physical Function surveys and appends their id to the table that was created in Step 2.
Step 5 is a select query that counts up the number of Ids, which represents the number of new surveys.
Step 6 turns warning back on.
The FOURTH Step: Exporting the data to Winsteps
The process to export the data of all 3 surveys is controlled by one macro. The macro consists of approximately 11 steps. The result of the macro is that 1 text file for each survey is written off to a specific network folder. The text file is in the exact format needed for placement of the data into the Winsteps control file. The only input required by the end-user is for them to hit the start button to initiate the macro. The macro takes about 1 minute to run completely.
Step 1 of the macro is to change the MS Access default so that all program warnings are turned off.
Steps 2-6 are a set of queries that select the survey data and ready it for export. Each survey requires 2 queries; currently we are analyzing 3 surveys so that is why this section has 6 steps. The following steps will describe the process for only one of the surveys with the understanding that the process for the others is separate but exactly the same.
The first query points at the table that supports the key-entry form for the Activated Pt survey. This table contains all of the responses for all of the Activated Pt surveys. This first query is a MakeTable query that grabs the unique id (a MS Access generated AutoNumber) of each completed survey as well as converts that id to a format that fits well into the Winsteps control file. The Winsteps control file requires the id field to be fixed width so the query reformats the id by adding 10,000,000 to each id that way the id will always be 8 digits long. For example, an id of 15 would become 10,000,015 and an id of 115 would become 10,000,115. These two fields are written off to a holding table. The purpose for this holding table is that it acts as a type of CrossWalk table that allows the pre-Winsteps and post-Winsteps survey results to be linked.
The second step of this two step process is another Make Table query that brings in this newly created 8 digit id and adds all of the responses to the survey question. These are responses that are in a numeric format, ie a response of Poor=1, Good=2, etc. This table has the sole purpose of holding the data in a place where it can be exported using predefined export specs. (A side note: if the responses were in a text format the functionality of converting them to numeric could easily be accomplished in this step by simply adding a link to a conversion table.)
As mentioned above the previous two steps are repeated 2 more times within the macro, once for each survey.
Steps 7-9 of the macro export the contents of each export table using a predefined export spec. Each text file is given a predefined name and written off to a predefined folder.
The last step of the macro turns the MS Access warning back on and produces a message notifying the user that export has been completed.
The FIFTH Step: Integrating Winsteps
Again, this set of steps is separate but parallel for each survey being analyzed. Winsteps is opened and the control file is opened for editing. The text file written off by the MS Access macro is opened in notepad. The contents of the notepad file are copied and pasted over the previous results in the Winsteps control file. The programming commands of the Control File are not pasted over, they are left as is. It is only the survey data that is replaced.
The end result is that the control file now contains all of the same results as it did previously with the addition of any new survey results that have come in since the last time the survey was analyzed. The Winsteps control file is saved and then run. Winsteps automatically writes off the necessary person file to a predefined folder with a predefined name.
The SIXTH Step: Cleaning the Winsteps Person File
MS Excel is used to open the just created Person File. The person file is cleaned up: all columns are deleted except for id, score and error. The file is saved as a text file. (Side note: this step will be looked at for possible automation.)
The SEVENTH Step: Import the results back into the MS Access dB
The MS Access import macro is an 8+ step process that automatically brings the results into the dB and creates a report listing the results of only the new surveys. “New” meaning surveys that have come in since the last time this analysis had been run. This macro takes ~~3 min to run.
Step 1 of the macro turns all warning off.
Steps 2-4 imports the cleaned up person files into 3 tables, one for each survey. There is an index on each table to prevent duplicate entries. This index prevents any unique id to come into the table if it is already there. At the end of step 4 there is 1 table for each survey. In each table is unique id, score, and date stamp. For the surveys that are new the date stamp field is blank.
Steps 5-7 run a query for each survey that places a date stamp, today’s date, in the blank fields of the new surveys. This date stamp is required for the next step to function properly.
Step 8 creates a report that is based off of the 3 score tables. This report grabs all survey scores, survey ids, and patient names for all new surveys. This is why the date stamp field is critical as well as the previously created Crosswalk tables. The actual score table only has 3 fields in it: unique id, score, and date stamp. The crosswalk tables are used to grab patient name and medical record numbers.
The last step turns all warning back on.
The EIGTH Step:
Now, the user has a report that lists the results of all new patient surveys. This printout is used to update an SPSS file that is used to graphically represent the patient’s results of all surveys over time.
REVIEW
To set up this automation in its entirety took approximately 16 hours. This investment will pay for itself within 9-10 weeks as we usually analyze new surveys 1x per week and that analysis would have taken approximately 2hrs each time. Above and beyond the gained efficiency, the automation of this process has made the analysis less prone to error. Multiple manual steps have been reduced down to a few with the remaining “work” being done automatically. Another large benefit is that all survey data is now being stored in a stable environment and it is being stored in a way that allows us to easily extract it. We are able to quickly and easily extract the data in just about any iteration and format that is needed. A single patient can be linked multiple ways to multiple surveys and multiple scores. This database will provide us the flexibility required to meet the reporting needs that have not yet been anticipated.
A simple tutorial has been created that will allow just about any analyst to step in and adequately perform the analysis. Prior to this it would have taken several days of training and extensive documentation for someone unfamiliar with the analysis to complete it from start to finish. In a work environment where task and assignments are shared with a partner, the simplification and standardization of any process is a huge benefit.
