How to write value to a particular column based on condition

[aalluru]

I have the following dataset:

ID   val1   val2   val

1    1        2        1

1    1        2        2

This dataset was obtained by joining with the table the val is present it and matching with either val1=val or val2=val. Since both matched, there are 2 rows.

I would like to transform this into a single row per id as follows so that all the values in the val column are shown in the same row:

ID   val1   val2   val1match   val2match

1     1       2        1                 2

If for a certain ID, there is only one match and hence there is only one row to begin with like this:

ID   val1   val2   val

2    1        2        1

that should be transformed as follows:

ID   val1   val2   val1match   val2match

2     1        2        1                 .

[Reeza · Accepted solution]

Transpose the data.

proc transpose data=have out=want prefix=match_val;
by id val1 val2;
var val;
run;

---

@aalluru wrote:

I have the following dataset:

ID   val1   val2   val

1    1        2        1

1    1        2        2

This dataset was obtained by joining with the table the val is present it and matching with either val1=val or val2=val. Since both matched, there are 2 rows.

I would like to transform this into a single row per id as follows so that all the values in the val column are shown in the same row:

ID   val1   val2   val1match   val2match

1     1       2        1                 2

If for a certain ID, there is only one match and hence there is only one row to begin with like this:

ID   val1   val2   val

2    1        2        1

that should be transformed as follows:

ID   val1   val2   val1match   val2match

2     1        2        1                 .

---

[aalluru]

Thanks for the response! I tried this out but I actually have multiple columns that I want to put in the var statement so it's giving me 1 row for each column that I put there
So basically, there's val and code. Each val has a corresponding code. I'm not trying to match the code with anything when I join but I just want that displayed there from this:
ID val1 val2 val code
1 1 2 1 3
1 1 2 2 4
to this:
ID val1 val2 val1match code1 val2match code2
1 1 2 1 3 2 4

The order of columns can differ but I just want them there

[Reeza]

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Or you could consider a tranpose to a longer format first and then another to a wide format. 
And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

You need to either transpose twice and merge or use a data step merge then where you can do it once. 

Proc transpose is a more dynamic approach whereas with a data step you need to know your maximum dimensions ahead of time. Alternatively, you may wan to consider backing up a step and redesign your process and merge to accommodate this ahead of time somehow. 

[aalluru]

yup! I figured out a way to do it differently. Thank you so much for your help!