topic Re: Find matching columns in SAS Programming

Find matching columns

raddad34 — Wed, 09 Mar 2022 17:13:23 GMT

I have a large dataset that looks like this:

data have;
input finalVal value1 value2 value3;
datalines;
5 1 2 5
6 26 6 10
29 4 9 1
7 35 1 7
;

I want to find which of the 3 columns match and output that to a new column

like this:

data want;
input finalVal value1 value2 value3 matching_column $;
datalines;
5 1 2 5 value3
6 26 6 10 value2
29 4 9 1 none
7 35 1 7 value3
;

is this doable within a single proc sql or data step?

I was thinking I could make a series of if statements for everything, but I'm not sure if there is a more efficient way of doing this.

I would appreciate any input, thanks!

Re: Find matching columns

PaigeMiller — Wed, 09 Mar 2022 17:26:01 GMT

I don't understand the logic here. Please explain further. I also don't see where any "matching columns" comes into play.

Re: Find matching columns

Reeza — Wed, 09 Mar 2022 17:27:15 GMT

Do you want the variable name or just the index?

data want;
set have;

array _search(3) value1-value3;

index = whichn(finalVal, of _search(*));
if index ne 0 then variableName = vname(_search(index));
else variableName = 'none';

run;

@raddad34 wrote:

I have a large dataset that looks like this:
data have;
input finalVal value1 value2 value3;
datalines;
5 1 2 5
6 26 6 10
29 4 9 1
7 35 1 7
;
I want to find which of the 3 columns match and output that to a new column

like this:
data want;
input finalVal value1 value2 value3 matching_column $;
datalines;
5 1 2 5 value3
6 26 6 10 value2
29 4 9 1 none
7 35 1 7 value3
;
is this doable within a single proc sql or data step?

I was thinking I could make a series of if statements for everything, but I'm not sure if there is a more efficient way of doing this.

I would appreciate any input, thanks!

Re: Find matching columns

pink_poodle — Wed, 09 Mar 2022 17:47:46 GMT

It is doable. There is a varname function that returns variable name. Please look at the example here:
https://documentation.sas.com/doc/en/sclref/9.4/p16g1sss185es1n1k1dhbxhsdqi2.htm