Hello, I'm trying to change a table from narrow to wide. What I have:
Col1 | Col2 | Col3 |
a | foo_a | val_1 |
a | foo_b | val_2 |
b | foo_a | val_1 |
b | foo_b | val_2 |
What I want:
Col1 | Col2_1 | Col2_2 | Col3_1 | Col3_2 |
a | foo_a | foo_b | val_1 | val_2 |
b | foo_a | foo_b | val_1 | val_2 |
I want to do it programmatically, since the unique values in Col2 and Col3 vary. Your help is appreciated!
Thanks. Yes, for sure the actual data set will have many unique values in Col2, Col3, Coln for each distinct value in Col1. And the reason for the transformation is that I need to join 3 tables, and what's happening is the final table ends up being ~450m rows because the join fields occur multiples times. Does that make sense? Which is why I need to convert from narrow to wide
Please post examples of all datasets in usable form and show the expected result of merging those datasets.
Yes, PROC TRANSPOSE as recommended by @AMSAS can help. But keep in mind that the resulting data set could easily be bigger than the original data set. The wider data set would contain fewer rows, but many more columns. Take a look at the sample data you posted, where you start with 12 data elements and end up with 10. That's a small reduction. But keep in mind that you can have a different number of rows in the narrow data set for each COL1. So in your sample data, if there were only one COL1 row for "a" you would be starting with 9 data elements in total, but ending up with 10 (two of which would have missing values). The number of variables in the wide data set will match the COL1 value with the largest number of rows, creating missing values for other COL1 values that don't have so many rows.
Still, you could end up saving space depending on the number of variables in the other tables that you are joining.
If you want to simultaneously transpose multiple variables then you probably want to use the IDGROUP feature of PROC SUMMARY. You will need to tell it in advance the number of new variables to create (the maximum number of observations per ID group).
Search: https://www.google.com/search?q=%40sas.com+idgroup+transpose
Number one hit is the article by @data_null__
https://support.sas.com/resources/papers/proceedings10/102-2010.pdf
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: