4 Replies Latest reply on Jun 26, 2017 4:02 PM by bryanade

    Exporting longitudunal data


      My research database has a few tables, but I wanted to ask about how to handle longitudinal data in an export to Excel (since it's the intermediary file format to my statistical package.)


      Each subject in our study is entered into a Subjects table to store their demographic information like year of birth and gender. There is no identifying information in this database.


      Whenever a subject comes in for a follow-up visit, their measurements are entered into a Measurements table. The Subjects and Measurements table are linked via a unique ID. The date of their follow-up visit is recorded in the Measurements table, since any given subject will have several measurements taken.


      For one of our analyses, we need to separate out the follow-up measurements into separate columns. To do this, we would specify a date range and then classify the follow-up into a category (e.g. anywhere from 6-18 weeks would be classified as a 12-week follow-up)


      What I need is to export the data such that for each Subject (or unique ID), there is a Baseline measurement and then a 12-week follow-up and a 52-week follow-up in each row. So, the column headings would be "SubjectID", "Baseline measurement", "12-week follow-up" and "52-week follow-up", as opposed to how things are now, where the headings are "Subject ID", "Date of follow-up", "Measurement"


      I feel like this is a common problem, but haven't been able to search my way to a solution in the archives.

        • 1. Re: Exporting longitudunal data

          In simple terms, you can move the data into a table specifically configured for export. You can name the fields in this table as needed to get your column headings and combine data from different records (rows) into a single row where each record populates a different column.


          I would guess that there are also ways to do this with an XML report by setting up the appropriate XSLT grammar.

          • 2. Re: Exporting longitudunal data

            Thanks Phil.


            In order to do the "simple", I'd have to categories my follow-ups within the Measurement table though, correct? Otherwise, I suppose I could put the categorization rules in the field definition of the new table...

            • 3. Re: Exporting longitudunal data

              It seems you are basing your data on the time between checkups.


              You could add a field for date of last visit to your record and then determine the number of weeks based on

              current date - previous date / 7 or something similar. Now each record would have the week number you are looking for.


              You can find and sort the patients record by date and start with the first record and capture its date and go to the second record and paste. Capture the date of the second and go to the third, etc.


              Does this sound like what you need?

              • 4. Re: Exporting longitudunal data

                Actually, it's from the time of their first treatment. We just can't pin people down to come in at _exactly_ 3 months (for example), so it can be off by 4 weeks on either side.