Extract "Text" condition?

[ybz12003]

Hello, 

I have a sample dataset that has Name and Format columns, and both of them are character columns. I would like to do the following steps to create a new dataset to match the following conditions.

if format = '$';
if Name not in ('id', 'record');
if Name not end in ('sp');

I am curious if there is a fast way to do so, cause my code is long.

[ballardw]

Is your "format" variable actually only 1 character? If not, the snipped you show likely will fail.

Note that you might need to consider case. If the name is actually  "ID" or "Id" then you If would not perform as expected.

And since the following is meaningless I suggest you provide some details.

if Name not end in ('sp');

If that is supposed to identify values of the Name variable that finish with the letters 'sp' then you are looking for a combination of the INDEX and Length functions since you need to know if 'sp' occurs in specific characters. And again you have to worry about case of the letters as 'Sp' 'SP' or 'sP' would not be matches.

I think you might be looking more for deletion of records conditionally but you would have to provide rules for the actual condtions.

It might help if you could show a small example of what you have and what you expect.

If you built that data set perhaps you should go back a step or two and share how it was build as well.

[ybz12003]

I would like to get the 'uairway' in the ''Name" column, excluding "uairwayothsp."

[ballardw]

---

@ybz12003 wrote:
I would like to get the 'uairway' in the ''Name" column, excluding "uairwayothsp."

---

Pretty incomplete description.

Do you mean "because the name ended in sp I want the first 7 letters"? Or is there another OBSERVATION that contains "uairway"?

You also have not addressed the possible upper /lower case comparison issue either.

Really, provide data. input and expected output.

[Tom · Accepted solution]

This will be easier with a WHERE statement since since you can use the LIKE operator.

where format = '$'
  and not (upcase(name) in ('ID' 'RECORD'))
  and not (upcase(trim(name)) like '%SP'))
;

If you actually want any format that starts with $, such as $CATEGORY. or $100. then use the colon modifier after the equal sign.

[ybz12003]

I don't know there is ike '%SP' function. It seems like it a very helpful and short cut way to do the text fragment searching.

[ballardw]

---

@ybz12003 wrote:
I don't know there is ike '%SP' function. It seems like it a very helpful and short cut way to do the text fragment searching.

---

LIKE is allowed only in WHERE statements (borrowed from SQL) and has some other behaviors if the characters _ and % are used in different places.

Regular expressions in functions like PRXCHANGE, PRXMATCH, PRXPARSE allow more complex manipulation. BUT you have to clearly describe the manipulation or search rule(s) involved. That was the point of my previous comments. You were not clearly stating rules but providing an example.

If I provide an example like:

Input   Output

1        3

2        3

3        5

4        4

without any rule how do you determine the output for input values of 13 or 27 or 444 or infinity?