topic SQL to remove only 00 in a string in SAS Programming

SQL to remove only 00 in a string

Babloo — Tue, 23 Jan 2024 14:18:53 GMT

Any help to remove 00 from the records below via proc sql? I want to display blanks if the records has multiple 00s without any alphabets

00,CQ,GC
00,RT,00,IR
00,00
RT,GT,KL
100,1000,00,RT

Excepted Result:

CQ,GC
RT,IR

RT,GT,KL
100,1000,RT

Re: SQL to remove only 00 in a string

FreelanceReinh — Tue, 23 Jan 2024 16:39:17 GMT

Hello @Babloo,

Start with the Perl regular expression below ...

data have;
input c $char60.;
cards;
00,CQ,GC
00,RT,00,IR
00,00
RT,GT,KL
100,1000,00,RT
;

proc sql;
create table want as
select prxchange('s/,00\b|\b00\b,?//',-1,c) as c length=60
from have;
quit;

... and then modify it if necessary, e.g., to include space characters:

select prxchange('s/,\s*00\b\s*|\s*\b00\b,?\s*//',-1,c) as c length=60

Re: SQL to remove only 00 in a string

Patrick — Wed, 24 Jan 2024 10:36:12 GMT

I believe for your sample data the 2nd expression from the OR condition @FreelanceReinh proposes is sufficient. Because you wrote MULTIPLE and not EXACTLY TWO zeros I've also added a + (one or many) after the second zero.

select prxchange('s/\b00+\b,?//',-1,trim(c)) as c length=60