# What is the best way to prepare a database export to a statistician for statistical analysis?

I work in Rome for the Italian National Health Service (www.iss.it); I am currently working on a European clinic project called EMTICS (www.emtics.eu), I developed both the database and the website to collect clinician data. I need an advice on how to export data from SQL database to a file that will be then analyzed by STATA or SPSS. A quick summary: the clinician study is about TICS in child. Data is about 1,000 patients all over the Europe. Every patient can do a number of visits and in every visit about 700 variables are recorded. Every visit is also identified by a “VisitType” code (BS=baseline; UP=follow up;FC=final; etc.) and by a “VisitNumber. For example, if we consider the variable “ThroatSwab”, I have the dataset:


| idPatient | VisitType | VisitNumber | ThroatSwab | ... | other variable | ... |
| 15 | BS | 01 | 1 | ... | other value | ... |
| 15 | UP | 01 | 1 | ... | other value | ... |
| 15 | UP | 02 | 1 | ... | other value | ... |
| 15 | UP | 03 | 1 | ... | other value | ... |
| 15 | FC | 01 | 1 | ... | other value | ... |
| 44 | BS | 01 | 0 | ... | other value | ... |
| 44 | UP | 01 | 1 | ... | other value | ... |
| 44 | UP | 02 | 0 | ... | other value | ... |
| 44 | UP | 03 | 0 | ... | other value | ... |
| 44 | FC | 01 | 0 | ... | other value | ... |
..................................

I don’t understand why the statistician in London asked for a dataset like the following:

| idPatient | BS01_ ThroatSwab | UP01_ ThroatSwab | UP02_ ThroatSwab | UP03_ ThroatSwab | FC01_ ThroatSwab | ... | BS01_ ThroatSwab |...|
| 15 | 1 | 1 | 1 | 1 | 1 | ... | other value | ... |
| 44 | 0 | 1 | 0 | 0 |0 | ... | other value | ... |
......................................................

| Variable_Label | VisitType | VisitNumber | Variable_Name |
| ThroatSwab | BS | 01 | BS01_ ThroatSwab |
| ThroatSwab | UP | 01 | UP01_ ThroatSwab |
| ThroatSwab | UP | 02 | UP02_ ThroatSwab |
| ThroatSwab | UP | 03 | UP03_ ThroatSwab |
| ThroatSwab | FC | 01 | FC01_ ThroatSwab |
..................................................................


Where there are different variable names on every different visit type and visit number... Plus a colum “Variable_Label” that group similar variables…

In other words: I'm not the person that have to analyze the data with SPSS or R: I must simply provide the dataset to a statistician. The first dataset sample indicated is the result of complex processing operations (transposed and other stuff) so I was wondering, as it is relatively simple to make groupings with R or SPSS: is not better to provide the statistician the dataset already just as it is (thus the visit as single statistical unit, than to waste time re-codind the original data? Now the file is about 10,000 lines (the visits: 10 visits per patients per 1000 patients) and 700 columns (variables recorded at each visit), while the statistician wants a file with 1000 lines (the patients) and 7000 columns (different variables for each kind of visit type and number.). I really can't figure out how can he remap multiple observations of the same variable that would have different names for each visit.

Is there a clear reason because the statistician wants data in this way (for example the software he will use)? Or is it only an unnecessary complication?

Forgive me for my English, I hope I was clear. Thank you in advance, Marco

Is there a clear reason because the statistician wants data in this way (for example the software he will use)?

It's indeed software (sort of). SPSS would use (for most analysis at least) a so called wide data format (table 2 from your example) - that is: each variable has it's own column and each observation (eg. participant) is in one row. In R on the other hand one would (most likely) prefer a so called long data format (table 1 from your example). That said both software provide tools for data manipulation (aggregate and / or restructure in SPSS for example) allowing for a quick (or not so quick if data is messy) change from one format to an other.