Hello! I'm working on cleaning up a dataset for analysis for my research team. However, transforming data is unfortunately my weakest skill in SAS. My graduate program prepared me really well for analysis and general data cleaning, but barely skimmed transforming and transposing data. The data I've worked with at my post-grad job so far has mostly been in the right format, with only mild transformations needed until now. I'm trying to teach myself data transformation and I've referenced The Little SAS Book multiple times but I'm unfortunately stumped with this type of transformation. I have a dataset where the key identifier is ID. Most variables have one row of data. However, some variables have multiple rows of data. This leads to blank rows of data for the rest of the variables. I want to transform the data so a column with multiple rows is now in various columns, rather than rows. Here's an example with fake data of what my dataset currently looks like: ID age dx weight test 1 29 covid 140 covid blank blank flu blank blood pressure blank blank diabetes blank blank 2 33 high bp 165 covid blank blank gest diab blank blood pressure blank blank preeclamp blank blank So what I want to do is have 1 row of data for each unique ID. Each repeating instance for variables with multiple rows of data (like dx or test) would then be something like dx_1 dx_2 dx_3, etc and test_1 test_2 test_3. At first, I attempted to just delete the blank rows, which did work. But it consolidated all of the repeating instances into one row, which will cause issues for analysis. Therefore, I need to transpose them into new columns. I would greatly appreciate some guidance. I am trying to master the skill of transforming data so that I can effectively do it on my own in the future. Thank you so much for your help!
... View more
