topic Re: Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”. in SAS Programming

Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”.

Alexxxxxxx — Tue, 19 Feb 2019 16:52:45 GMT

Hello,

In a company name standardisation process, I expect to 'Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”. and Remove all other punctuation.'

So, I would like to do it by following steps,

1. split a variable into several independent variables.

2. Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”.

3. Remove all other punctuation.

4. merge them together ( both keep the original variable and new variable).

5. create a new variable to remind me of what the change happen (i.e., 'convert to &', 'remove punctuation' or 'both')

Could you please give me some suggestion about this?

Thanks for your attention to this matter.

Re: Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”.

novinosrin — Tue, 19 Feb 2019 16:57:15 GMT

Can you please post a sample data of what you HAVE and WANT?

Re: Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”.

Reeza — Tue, 19 Feb 2019 17:48:47 GMT

@Alexxxxxxx wrote:

Hello,

In a company name standardisation process, I expect to 'Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”. and Remove all other punctuation.'

So, I would like to do it by following steps,

1. split a variable into several independent variables.

2. Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”.

3. Remove all other punctuation.

4. merge them together ( both keep the original variable and new variable).

5. create a new variable to remind me of what the change happen (i.e., 'convert to &', 'remove punctuation' or 'both')

Could you please give me some suggestion about this?

Thanks for your attention to this matter.

1. SCAN(), SUBSTR() or PRX()

2. TRANWRD()

3. COMPRESS()

4. CATT()

5. No idea.

Check character functions here:

https://documentation.sas.com/?docsetId=nlsref&docsetTarget=p0ydidiqibf0vbn1jljj1cv9men9.htm&docsetVersion=9.4&locale=en

Re: Convert “AND”, “ET”, “Y”, “UND” and so on, to “&”.

PGStats — Tue, 19 Feb 2019 17:52:22 GMT

Use prxChange:

data _null_;
length text newText $64;
do text = "Procter and Gamble", "Greyhound Lines", "Moët et Chandon";
    newText = prxChange("s/\b(AND|ET|Y|UND)\b/&/io", -1, text);
    put (_all_) (=);
    end;
run;

text=Procter and Gamble newText=Procter & Gamble
text=Greyhound Lines newText=Greyhound Lines
text=Moët et Chandon newText=Moët & Chandon

Compare text and newText to detect any change.