topic Re: How to write value to a particular column based on condition in SAS Programming

How to write value to a particular column based on condition

aalluru — Wed, 09 Feb 2022 16:37:21 GMT

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 .

Re: How to write value to a particular column based on condition

Reeza — Wed, 09 Feb 2022 16:48:23 GMT

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 .

Re: How to write value to a particular column based on condition

aalluru — Wed, 09 Feb 2022 17:02:35 GMT

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

Re: How to write value to a particular column based on condition

Reeza — Wed, 09 Feb 2022 17:51:51 GMT

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.

Re: How to write value to a particular column based on condition

aalluru — Wed, 09 Feb 2022 21:34:57 GMT

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