topic Re: Finding Special Characters using Proc SQL in SAS Programming

Finding Special Characters using Proc SQL

JM_VFAU — Mon, 20 Feb 2017 23:45:26 GMT

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?

Re: Finding Special Characters using Proc SQL

art297 — Tue, 21 Feb 2017 00:28:25 GMT

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

Re: Finding Special Characters using Proc SQL

Ksharp — Tue, 21 Feb 2017 02:49:58 GMT

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;

Re: Finding Special Characters using Proc SQL

PGStats — Tue, 21 Feb 2017 04:25:17 GMT

Function anycntrl detects control characters:

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

Re: Finding Special Characters using Proc SQL

JM_VFAU — Tue, 21 Feb 2017 04:53:00 GMT

Thanks. I will try this option.

Re: Finding Special Characters using Proc SQL

JM_VFAU — Tue, 21 Feb 2017 04:54:22 GMT

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.