BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aalluru
Obsidian | Level 7

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                 .

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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                 .


 

View solution in original post

4 REPLIES 4
Reeza
Super User

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
Obsidian | Level 7
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
Super User

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
Obsidian | Level 7
yup! I figured out a way to do it differently. Thank you so much for your help!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1131 views
  • 0 likes
  • 2 in conversation