DATA Step, Macro, Functions and more

Text String, Search and Replace, Contingent

Accepted Solution Solved
Reply
Regular Contributor
Posts: 223
Accepted Solution

Text String, Search and Replace, Contingent

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

 

 


Accepted Solutions
Solution
‎07-19-2016 12:23 AM
Super User
Posts: 10,035

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

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;

View solution in original post


All Replies
Super User
Posts: 19,822

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

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. 

 

 

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p0w6napahk...

Regular Contributor
Posts: 223

Re: Text String, Search and Replace, Contingent

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.

 

 

Super User
Posts: 10,035

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

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;
Regular Contributor
Posts: 223

Re: Text String, Search and Replace, Contingent

But daaaad.....

 

Error message.

 

The data set WORK.WANT has 0 observations and 1 variables.

 

As said, seems pretty hard.

 

 

Super User
Posts: 10,035

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

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;

Regular Contributor
Posts: 223

Re: Text String, Search and Replace, Contingent

[ Edited ]

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.

 

 

Solution
‎07-19-2016 12:23 AM
Super User
Posts: 10,035

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

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;
Regular Contributor
Posts: 223

Re: Text String, Search and Replace, Contingent

[ Edited ]

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

 

 

Super User
Posts: 19,822

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

Delete was ambiguous, could mean the observation or character. 

Respected Advisor
Posts: 3,156

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

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;
Regular Contributor
Posts: 223

Re: Text String, Search and Replace, Contingent

[ Edited ]

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

 

 

Respected Advisor
Posts: 3,156

Re: Text String, Search and Replace, Contingent

Posted in reply to NicholasKormanik

Well, you are not the only one, I am with you. I refuse to read it after I write it Smiley Happy. You know what they say about Perl Regular Expression? OK to write, Hard to read.

 

 

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 501 views
  • 6 likes
  • 4 in conversation