Filter only the underscore values and without any special characters using WHERE caluse

[David_Billa]

I'm using the code below to filter only the underscore values and without any special characters using WHERE caluse, But with the following code I'm not getting the desired output;

data have;
input TBL $20.;
datalines;
(INS_CON_GRP)
INS_GRT
INS
H_EAL_YE_A
INS_CON_GROUP_DETL
;

data want;
   set have;
   where countc(TBL, '_') ge 2;
run;

Desired Output is:

H_EAL_YE_A

INS_CON_GROUP_DETL

[KachiM]

Change GE by GT.

[David_Billa]

No, it is not resolving the issue if I change the example data. I ran the code below.

data have;
input TBL $20.;
datalines;
(INS_CON_GRP)
INS_GRT
INS_GR_T
INS
H_EAL_YE_A
(INS_CON_GROUP_DETL)
;

data want;
   set have;
   where countc(TBL, '_') gt 2;
run;

Output which I got is,

H_EAL_YE_A
(INS_CON_GROUP_DETL)

Desired Output is,

H_EAL_YE_A

INS_GR_T

[mkeintz · Accepted solution]

You test for 2 or more underscores, but you didn't create a where condition to apply "without any special characters".

Let's say  special characters are anything except letters, numbers, or underscores. Then:

data have;
input TBL $20.;
datalines;
INS_GRT
INS
H_EAL_YE_A
INS_CON_GROUP_DETL
;

data want;
   set have;
   where countc(TBL, '_') ge 2  and findc(trim(TBL),' ','ADFK')=0;
run;

The FINDC function is the interesting element  here.

It ordinarily searches the first argument,  trim(TBL), for any character in the second argument and returns the position of the first qualifying character in TBL.  If none is found it returns a zero.

But

1. When the 2nd argument is blank it is ignored
2. The third argument has modifiers
   1. A  - search for any letter
   2. D - search for any digit
   3. F - search for any underscore
3. But  what is wanted is not the position of the first letter, digit, or underscore, but the position of the first character that is NOT one of those.  That is done by use of the K in the third argument.  So it finds the position of the first non-letter, non-digit, non-underscore.  If the function returns a zero there are no special characters.

Edited additional note: @Ksharp contributed an even simpler solution:

data want;
   set have;
   where countc(TBL, '_') ge 2  and notname(strip(TBL))=0;
run;

The NOTNAME function  detects any character not eligible for a version 7 sas variable name, i.e. not a letter, number, or underscore.

[David_Billa]

Excellent!

[Ksharp]

data have;
input TBL $20.;
datalines;
INS_GRT
INS
H_EAL_YE_A
INS_CON_GROUP_DETL
;

data want;
   set have;
   where countc(TBL, '_') ge 2  and notname(strip(TBL))=0;
run;

[mkeintz]

"notname" function. I knew somewhere in the vast array of functions, there was something having to do with varname-eligible characters. Nice.