Finding Special Characters using Proc SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Finding Special Characters using Proc SQL

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? 


Accepted Solutions
Solution
‎02-20-2017 11:52 PM
Respected Advisor
Posts: 4,609

Re: Finding Special Characters using Proc SQL

Function anycntrl detects control characters:

 

proc sql;
select *, anycntrl(Frst_nm) > 0 as flag
 from have;
quit;
PG

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,302

Re: Finding Special Characters using Proc SQL

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

Occasional Contributor
Posts: 13

Re: Finding Special Characters using Proc SQL

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.

Grand Advisor
Posts: 9,596

Re: Finding Special Characters using Proc SQL

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;

Occasional Contributor
Posts: 13

Re: Finding Special Characters using Proc SQL

Thanks. I will try this option.

Solution
‎02-20-2017 11:52 PM
Respected Advisor
Posts: 4,609

Re: Finding Special Characters using Proc SQL

Function anycntrl detects control characters:

 

proc sql;
select *, anycntrl(Frst_nm) > 0 as flag
 from have;
quit;
PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 225 views
  • 0 likes
  • 4 in conversation