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
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
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
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.