I want to use Proc SQL to find out if there are any data in the column with Vertical Tab, Line Feed, Horizontal Tab, Carraige Return.
When running in Teradata directly i use the below query
SyntaxEditor Code Snippet
INDEX(Last_Nm, '09'XC ) > 0 -- Horizaontal tabOR INDEX(Last_Nm, '0A'XC ) > 0 -- Line FeedOR INDEX(Last_Nm, '0B'XC ) > 0 -- Vertical tabOR INDEX(Last_Nm, '0D'XC ) > 0 -- Carrriage Return
I used the below conditions in SAS EG inside a Proc SQL and it does not work.
(index(Frst_Nm,'09'x) >0) /*Horizontal Tab*/
Or index(Frst_Nm, '0A'x) > 0 /* Line Feed*/
Or index(Frst_Nm, '0B'x) > 0 /*Vertical tab*/
Or index(Frst_Nm, '0D'x) > 0 /*Carrriage Return*/
)
Are there any other ways?
Function anycntrl detects control characters:
proc sql;
select *, anycntrl(Frst_nm) > 0 as flag
from have;
quit;
You have an extra parenthesis in your first use of the index function. The following worked for me:
data Have; length Frst_Nm $30.; i=1; Frst_Nm=catt('John','09'x,'Junk'); output; i=2; Frst_Nm=catt('John','0A'x,'Junk'); output; i=3; Frst_Nm=catt('John','0B'x,'Junk'); output; i=4; Frst_Nm=catt('John','0D'x,'Junk'); output; i=5; Frst_Nm=catt('John','Junk'); output; run; proc sql; select *, (index(Frst_Nm,'09'x) >0 /*Horizontal Tab*/ Or index(Frst_Nm, '0A'x) > 0 /* Line Feed*/ Or index(Frst_Nm, '0B'x) > 0 /*Vertical tab*/ Or index(Frst_Nm, '0D'x) > 0 /*Carrriage Return*/ ) as test from have ; quit;
Art, CEO, AnalystFinder.com
I may have put an extra bracket when typing up the post. I am using this code in the SAS CI as an exclusion list. but it seems to be working sometimes but not when the campaign is scheduled.
It is easy for Perl Regular Expression. data Have; length Frst_Nm $30.; i=1; Frst_Nm=catt('John','09'x,'Junk'); output; i=2; Frst_Nm=catt('John','0A'x,'Junk'); output; i=3; Frst_Nm=catt('John','0B'x,'Junk'); output; i=4; Frst_Nm=catt('John','0D'x,'Junk'); output; i=5; Frst_Nm=catx(' ','John','Junk'); output; run; proc sql; select *,case when prxmatch('/\s/',compress(frst_nm,' ')) then 1 else 0 end as flag from have; quit;
Thanks. I will try this option.
Function anycntrl detects control characters:
proc sql;
select *, anycntrl(Frst_nm) > 0 as flag
from have;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.