topic Re: Filter only the underscore values and without any special characters using WHERE caluse in SAS Programming

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

David_Billa — Fri, 22 Nov 2019 05:55:40 GMT

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

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

KachiM — Fri, 22 Nov 2019 06:23:26 GMT

Change GE by GT.

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

David_Billa — Fri, 22 Nov 2019 06:36:17 GMT

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

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

mkeintz — Sun, 24 Nov 2019 05:14:18 GMT

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

When the 2nd argument is blank it is ignored
The third argument has modifiers
1. A - search for any letter
2. D - search for any digit
3. F - search for any underscore
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.

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

David_Billa — Fri, 22 Nov 2019 06:52:20 GMT

Excellent!

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

Ksharp — Fri, 22 Nov 2019 11:28:15 GMT

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;

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

mkeintz — Fri, 22 Nov 2019 19:00:44 GMT

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