BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NKormanik
Barite | Level 11

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

12 REPLIES 12
Reeza
Super User

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...

NKormanik
Barite | Level 11

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.

 

 

Ksharp
Super User

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;
NKormanik
Barite | Level 11

But daaaad.....

 

Error message.

 

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

 

As said, seems pretty hard.

 

 

Ksharp
Super User

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;

NKormanik
Barite | Level 11

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.

 

 

Ksharp
Super User

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;
NKormanik
Barite | Level 11

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

 

 

Reeza
Super User

Delete was ambiguous, could mean the observation or character. 

Haikuo
Onyx | Level 15

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;
NKormanik
Barite | Level 11

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

 

 

Haikuo
Onyx | Level 15

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.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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