Hello,
I am trying to determine the best way to handle programming for a clinical data set I have. Below is a simplified view of the current structure of the data I have. What I want is a transposed version of it. The reason I haven't been able to figure out how to transpose it is because a client can have multiple assessments at the 'PRN' timepoint. For similar data, I have been able to output separate datasets based on timepoints and then merge them back together (which worked well because there was one assessment per timepoint - no 'PRN'). I also am not sure how to best handle var1-4 (these are 4 variables that are for each assessment) - which has also made this difficult. There should be one row per unique ClientID. In addition to this, I would like to (eventually) be able to bring the dataset in SAS and update a master existing SAS dataset (based on the desired structure) when a new ClientID is detected OR there is a new survey for an existing client. Any insight would be greatly appreciated.
Current Structure:
| ClientID | timepoint | surveydate | totalscore | var1 | var2 | var3 | var4 |
| 1 | 5th-7th Pregnancy Visit | 6/1/2022 | 1 | Yes | no | yes | yes |
| 1 | 12 Weeks Postpartum | 6/2/2022 | 2 | Yes | no | yes | yes |
| 1 | Child 16 Months | 6/3/2022 | 3 | Yes | no | yes | yes |
| 1 | prn | 6/4/2022 | 7 | Yes | no | yes | yes |
| 1 | prn | 6/5/2022 | 8 | Yes | no | yes | yes |
| 2 | 5th-7th Pregnancy Visit | 6/6/2022 | 8 | Yes | no | yes | yes |
| 2 | Child 16 Months | 6/7/2022 | 9 | Yes | no | yes | yes |
Desired Structure:
| Clientid | timepoint1 | surveydate1 | totalscore | var1 | var2 | var3 | var4 | timepoint2 | surveydate2 | totalscore2 | var1 | var2 | var3 | var4 | timepoint3 | surveydate3 | totalscore3 | var1 | var2 | var3 | var4 | timepoint4 | surveydate3 | totalscore4 | var1 | var2 | var3 | var4 | timepoint5 | surveydate5 | totalscore5 | var1 | var2 | var3 | var4 |
| 1 | 5th-7th Pregnancy Visit | 6/1/2022 | 1 | Yes | no | yes | yes | 12 Weeks Postpartum | 6/2/2022 | 2 | Yes | no | yes | yes | Child 16 Months | 6/3/2022 | 3 | Yes | no | yes | yes | prn | 6/4/2022 | 7 | Yes | no | yes | yes | prn | 6/5/2022 | 8 | Yes | no | yes | yes |
| 2 | 5th-7th Pregnancy Visit | 6/6/2022 | 8 | Yes | no | yes | yes | Child 16 Months | 6/7/2022 | 9 | Yes | no | yes | yes | | | | | | | | | | | | | | | | | | | | | |
