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,
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;
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: