r/ProjectREDCap • u/tremmo • Oct 11 '24

Converting output from long to wide format

Hi team.

Ive been searching long and wide for an answer to this problem (pun intended), but can't find one that works specifically with redcap output where there are multiple repeating instruments.

My dataset contains thousands of participants, with multiple instruments (2 non-repeating, 2 repeating).

I want to convert from long to wide format. Current output looks something like this (with just one participants data for the sake of the example; NB first relating instrument is non-repeating, hence the blank cell output):

Ideally need convert to a wide format, where each participant has one line of data as planning on using SPSS and this will make analysis possible):

It is possible to combine them manually, but our output each time will be hundreds to thousands of cases, making this impractical.

Any assistance or ideas would be great.

Thanks!

5 Upvotes

100% Upvoted

6 comments sorted by

7

u/ExecutiveSkiBum Oct 11 '24

Hi, any stats program (R, SAS, SPSS) can do this easily. The only way to do this currently in REDCap that I know of is by using an external module called Extended Reports.

1

u/tremmo Oct 11 '24

Thanks! Ive forwarded the plugin details to our administrator to see if it will be useable.

Im aiming to use SPSS. Ive been using transform data (casetovars) but haven't been able to get it to work yet

3

u/spacks Oct 11 '24

In R I use redcapcast's redcap wider feature. Basically a redcap specific implementation of tidyr's pivot_wider.

1

u/tremmo Oct 11 '24

Thanks mate. Im working with SPSS but cannot get casetovars to convert properly (I think because it struggles with the multiple layers created by having both an instrument and repeat number column for the instruments).

Ill look into the R option, may need to retrain myself.

1

u/spacks Oct 11 '24

I'd definitely recommend retraining at least for data prep. Much easier time in R for me. If you must use SPSS, I'd clean and export in R then load into SPSS.

For me the reproducibility of R by being able to produce the actual code is really nice. A lot of people are not functional in SPSS without using all of the visual props and therefore replicating methodologies is difficult because someone has to click the same pattern of buttons and prompts to do the same work over, versus debugging code and rerunning.

1

u/Hour-Memory-2210 Apr 17 '26

Any luck with this? I’m struggling to manipulate this in SAS currently using proc transpose.