topic Re: Removing non-standard text when cleaning data - Substring, PRXCHANGE? in SAS Enterprise Guide

Removing non-standard text when cleaning data - Substring, PRXCHANGE?

vbylsma — Tue, 07 Dec 2021 21:40:42 GMT

I am standardizing unit names, however many of them contain a non-standard indicator at the end of the unit name (it denotes geographically separated but part of the same unit). I need to remove this final string so these units can later be combined into one unit. Typically I would substring this, but the indicator 1) does not occur on every unit name, 2) is not in a standard position in the string, and 3) the final sting itself isn't consistent in the use of digit vs character starting in position 3 (see below).

Examples of the unit names:

SURGICAL OPS OLA900

SURGICAL OPS OLHC00

EDU AND TRNG OL0IZH

EDU AND TRNG OL0PZR

LEGAL OLCS00

LEGAL OLA200

The final sting always starts with " OL". Searches have brought me to PRXCHANGE, but as I've read in forums and found out myself, the syntax is particularly confusing. What is your best practice recommendation in this situation?

Re: Removing non-standard text when cleaning data - Substring, PRXCHANGE?

Reeza — Tue, 07 Dec 2021 21:44:41 GMT

Your examples all seem to have it at the end and same pattern. Can you provide a more representative example and also include what you're looking for as your final output.

Re: Removing non-standard text when cleaning data - Substring, PRXCHANGE?

Astounding — Wed, 08 Dec 2021 02:50:57 GMT

Given that the string always begins the final word, here's one way:

delete_here = index(unit, ' OL');
if delete_here then
unit = substr(unit, 1, delete_here);

Re: Removing non-standard text when cleaning data - Substring, PRXCHANGE?

Ksharp — Wed, 08 Dec 2021 11:44:15 GMT

data have;
input have $40.;
want=prxchange('s/\bOL\w+$//i',1,strip(have));
cards;
SURGICAL OPS OLA900
SURGICAL OPS OLHC00
EDU AND TRNG OL0IZH
EDU AND TRNG OL0PZR
LEGAL OLCS00
LEGAL OLA200
;

Re: Removing non-standard text when cleaning data - Substring, PRXCHANGE?

vbylsma — Wed, 08 Dec 2021 13:20:27 GMT

This also worked, thank you so much!

Re: Removing non-standard text when cleaning data - Substring, PRXCHANGE?

vbylsma — Wed, 08 Dec 2021 13:21:01 GMT

This worked AND I learned a lot about PRXCHANGE. Thank you!