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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.