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 .
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 .
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 .
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.