topic Re: Find a value in all columns of a table in SAS Programming

Find a value in all columns of a table

viona — Fri, 09 Jul 2021 21:08:47 GMT

Hello,

is there a simple technique to find all records in a table where a specific value existe in one or more column?

For example, I want to retreive all records from table "tab" where we have "HL"

data tab;
input num1 num2 char1 $ num3 char2 $ char3 $;
datalines;
1 2 HL 3 HL X
4 5 AA 6 HL Y
7 8 EE 9 DD Z
;
run;

out put ==>

1 2 HL 3 HL X
4 5 AA 6 HL Y

Thx,

Re: Find a value in all columns of a table

PaigeMiller — Fri, 09 Jul 2021 21:32:30 GMT

Since HL can only exist in character variables, we only search the character variables. I will use the WHICHC function to do the searching.

data want;
    set tab;
    if whichc('HL',of char:)>0 then output;
run;

Now, you made it easy, your character variable names all begin with the four letters char. If that's not the case in your real data, and you have character variables named GORILLA, SWAN, OCTOPUS, and so on, then instead of typing them all out, you could use something like this to search all character variables, regardless of their name:

if whichc('HL',of _character_)>0 then output;

Re: Find a value in all columns of a table

viona — Fri, 09 Jul 2021 22:48:18 GMT

Thx a lot this is really helpful.

Re: Find a value in all columns of a table

manojb — Fri, 28 Jan 2022 18:53:12 GMT

what if the value is a number and the variable is character.

I have a situation where i should find a number ''4543" in nearly 150 variables. variables are mixed of numeric and char.

could you please suggest