Narrow to Wide

[current_thing]

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!

[AMSAS]

Take a look at PROC TRANSPOSE 

Transpose Example 

[Astounding]

A few related questions:

Will you ever have more than two rows for a single COL1 value?

Which variables are character and which are numeric?

Why transform the data? In the majority of cases programming on narrow data is easier and more flexible.

[current_thing]

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

[andreas_lds]

Please post examples of all datasets in usable form and show the expected result of merging those datasets.

[Astounding]

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.

[Tom]

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