Hello Everyone
How to find records with only special characters (except comma, hyphen, fullstop) in a variable (splchar) with CLOB datatype.
But the main problem I faced is that the program captures null records along with my requirement. When I use the program (where schar is not null) to exclude null value, its not working.
Can I use any other function like byte (). Please help me.
proc sql;
create table abc
as select compbl(splchar) from table1; quit;
data abc;
schar = compress(compress(splchar,"'-,.", 'n')); run;
data abc;
set abc;
where schar is not null; run;
Your second step does not have a SET statement consequently the data set WORK.ABC will be replaced with a single observation.
You may also find that the functions ANYPUNCT, NOTPUNCT, FIND, and FINDC can be helpful in these types of searches.
SAS doesn't have a data type "CLOB". So in the moment the CLOB is "moved" to SAS it's a character variable with a max. length of 32KB. If your CLOB potentially contains more than 32K then you need to split it up into several SAS variables (it's the same with PL/SQL).
No more sure how this "splitting up" is done but I know that it's documented for SAS so this is just about Googling.
So once in SAS your question is: How to find special characters in a SAS character variable. Art mentioned some methods. Other ways are: use compress() with the switch for special characters but exclude the ones you accept - and then use a length function on the resulting string and if it's '0' you've got only special characters. Another way would be to use a Regular Expression together with one of SAS' PRX...() functions (several options here).
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.