How do I create a counting variable based on another column & transpose a table?

acm — Sat, 10 Jun 2023 23:27:06 GMT

Hi all,

I'd appreciate help with reformatting a clinical dataset. It records ~50 variables for multiple patients on multiple visits. So there are ~51 columns with one being an MRN or record number per patient. There are typically 6-7 rows of data per patient/MRN to report on variables taken at different visits. So 2 questions:

1)How can I generate a new incremental count variable that counts number of visits per patient MRN and resets to 0 if the MRN changes in another column?

2)Using this new count variable (and/or the MRN variable), how can I transpose the data so that I have 50x 7 variables in columns for each patient, with each patient now being a single row? Ex: if patient had weight and temp recorded for 7 visits, can I do multiple columns for weight1, weight 2, weight3,...weight7 and similarly for temp1, temp2...temp7?

Thx for the advice in advance!

MRN. Date of Visit. Ideal Counter (based on MRN change)

1001. 01/23/2020. 1
1001. 01/30/2020. 2
1001. 02/04/2020. 3
1001. 12/02/2015. 4
1001. 12/09/2015. 5
1001. 12/16/2015. 6
1001. 12/23/2015. 7
2350. 01/06/2016. 1
2350. 04/14/2016. 2
2350. 12/17/2018. 3
2350. 12/21/2018. 4
2350. 12/31/2018. 5
2350. 01/07/2019. 6
2350. 01/14/2019. 7
2311. 02/17/2016. 1
2311. 03/02/2016. 2
2311. 03/16/2016. 3
2311. 07/18/2016. 4
2311. 07/25/2016. 5
2311. 08/01/2016. 6
2311. 08/08/2016. 7
11223. 08/15/2016. 1
11223. 08/22/2016. 2

..... ... ...

Re: How do I create a counting variable based on another column & transpose a table?

MarkJSchwab — Fri, 12 Mar 2021 00:13:55 GMT

The formula for your counter column is just Col Rank(:DateOfVisit, :MRN).

Then the pivot is Tables -> Split -> Split By: Counter; Split Columns: Height, Weight; Group: MRN

topic How do I create a counting variable based on another column & transpose a table? in Discussions

How do I create a counting variable based on another column & transpose a table?

Re: How do I create a counting variable based on another column & transpose a table?