CODEWITHSUNDEEP
sas
pwinter303
pwinter303

Reputation: 13

SAS: Is It Possible to Use a Variable/Observation in RegEx or PRXPARSE

I have a data set that contains CITY and COUNTRY. Unfortunately, the COUNTRY has also been concatenated into the CITY field. It looks like this:


    CITY                           COUNTRY
    Mexico City Mexico             Mexico
    Providence  United States      United States
    London United Kingdom          United Kingdom

I need to strip the country name out of the city.

I could write a line like this for every single country:

CITY = PRXCHANGE('S/MEXICO\s+$//', 1, CITY);

but that seems ugly.

I'd like to be able to use the COUNTRY read from the data set in the PRXCHANGE statement. Something like 

CITY = PRXCHANGE('S/COUNTRY\s+$//', 1, CITY);

The above doesn't work because the code searches for the literal 'COUNTRY' instead of the value of the variable.

I tried to use a macro variable but (if I understand it correctly) it must be set in a prior data step, which doesn't help.

I've searched SO and google for the last couple hours but haven't been able to come up with any solutions. It seems like this should be doable.

Any suggestions would be greatly appreciated!

Upvotes: 1

Views: 167

Answers (1)

Tom
Tom

Reputation: 51621

Seems simple to me. You need to use an expression as the first argument instead of a constant.

CITY = PRXCHANGE(cats('S/',COUNTRY,'\s+$//'), 1, CITY);

Upvotes: 2

Related Questions