Here's the data:
1701_0-3101_1
1502a_1-1703_1
1703_1-1801_1
0101_1-1002_0
1205_1-1703_1
0501_1-1301_0
2401_0-3101_0
2006_0-3602_0
WXYZ_0-2803_0
LMNOP_0-2802_0
Two related problems:
1) IFF the third character over from the beginning is a ZERO, then delete it (or at the very least, change it to a space -- for compress to then kill it).
2) IFF, following the dash, the third character over from that is a ZERO, then delete it (or at the very least, change it to a space -- for compress to then kill it).
Appears to be a lot easier said than done.
Any help greatly appreciated.
Nicholas Kormanik
OK. Clear Now.
data have;
input x $40.;
cards;
1701_0-3101_1
1502a_1-1703_1
1703_1-1801_1
0101_1-1002_0
1205_1-1703_1
0501_1-1301_0
2401_0-3101_0
2006_0-3602_0
WXYZ_0-2803_0
LMNOP_0-2802_0
;
run;
data want;
set have;
temp=scan(x,1,'-');
if char(temp,3)='0' then temp1=cats(substr(temp,1,2),substr(temp,4));
else temp1=temp;
temp=scan(x,2,'-');
if char(temp,3)='0' then temp2=cats(substr(temp,1,2),substr(temp,4));
else temp2=temp;
want=catx('-',temp1,temp2);
drop temp:;
run;
Substr or char functions can help with your if conditions, they'll extract the character to be tested.
The scan function will help separate into parts before/after the dash.
Substr -left of- will allow you to replace the character.
I like to to find the functions I need by looking through the list, where it's listed by category.
Thanks Reeza.
I've been experimenting with the Substr, as it seems closest to being able to solve these. So far, nothing works.
My hunch is, time to study regular expressions a lot more. And find out how to use those in SAS. Probably a short string of that would do the trick.
Follow what @Reeza said . It is good for you to solve it on your own .
data have;
input x $40.;
cards;
1701_0-3101_1
1502a_1-1703_1
1703_1-1801_1
0101_1-1002_0
1205_1-1703_1
0501_1-1301_0
2401_0-3101_0
2006_0-3602_0
WXYZ_0-2803_0
LMNOP_0-2802_0
;
run;
data want;
set have;
if char(scan(x,1,'-'),3)='0' or
char(scan(x,2,'-'),3)='0' then delete;
run;
But daaaad.....
Error message.
The data set WORK.WANT has 0 observations and 1 variables.
As said, seems pretty hard.
That is what supposed to be.
You didn't post the output you need . So I guess you want OR .Try AND .
data want;
set have;
if char(scan(x,1,'-'),3)='0' and
char(scan(x,2,'-'),3)='0' then delete;
run;
Say what??!!
I did too. Delete!!
Here's the desired result (using a silly simple text editor):
171_0-311_1
152a_1-173_1
173_1-181_1
011_1-102_0
125_1-173_1
051_1-131_0
241_0-311_0
206_0-362_0
WXYZ_0-283_0
LMNOP_0-282_0
Whaddya think?
No need to do all in one step, by the way. Just do one at a time. Two separate steps? Even a partial achievement would be a plus.
OK. Clear Now.
data have;
input x $40.;
cards;
1701_0-3101_1
1502a_1-1703_1
1703_1-1801_1
0101_1-1002_0
1205_1-1703_1
0501_1-1301_0
2401_0-3101_0
2006_0-3602_0
WXYZ_0-2803_0
LMNOP_0-2802_0
;
run;
data want;
set have;
temp=scan(x,1,'-');
if char(temp,3)='0' then temp1=cats(substr(temp,1,2),substr(temp,4));
else temp1=temp;
temp=scan(x,2,'-');
if char(temp,3)='0' then temp2=cats(substr(temp,1,2),substr(temp,4));
else temp2=temp;
want=catx('-',temp1,temp2);
drop temp:;
run;
Xia Keshan, you da man! Wonderful work. I owe you, big time.
For that you can have ALL of the South China Sea. Except for what Haikuo wants (see post further below).
Hey, by chance do you happen to have a sister that's not married that you could introduce me to?
Nicholas
Delete was ambiguous, could mean the observation or character.
Or using PRX functions:
data have;
input x $40.;
cards;
1701_0-3101_1
1502a_1-1703_1
1703_1-1801_1
0101_1-1002_0
1205_1-1703_1
0501_1-1301_0
2401_0-3101_0
2006_0-3602_0
WXYZ_0-2803_0
LMNOP_0-2802_0
;
run;
data want;
set have;
x1=prxchange('s/(?<=^\w{2})0|(?<=-\w{2})0//oi',-1,x);
run;
Wow!! What magic is this??
So little code. Such instant amazing results.
How'd you do that??!!
Really, yours is a fantastic solution, as is Ksharp's above. I'll give Ksharp the edge only because no one in the world will understand your solution, even though it works perfectly.
Much thanks!
Nicholas
Well, you are not the only one, I am with you. I refuse to read it after I write it . You know what they say about Perl Regular Expression? OK to write, Hard to read.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.