topic Re: Spelling correction in SAS Programming

Spelling correction

BrahmanandaRao — Fri, 17 May 2019 07:30:55 GMT

Hi Guys,

I want to spelling correction in existing data

data trns;

x='JAPON';

m=translate('x','O','A');

run;

Re: Spelling correction

Kurt_Bremser — Fri, 17 May 2019 08:31:38 GMT

@BrahmanandaRao wrote:

Hi Guys,

I want to spelling correction in existing data

data trns;

x='JAPON';

m=translate('x','O','A');

run;

You already have working code. Where is the problem?

Re: Spelling correction

ErikLund_Jensen — Fri, 17 May 2019 09:22:05 GMT

Hi @BrahmanandaRao

You are not translating the value of variable X, but the literal string 'x', because you have the variable name in quotes, so the translate function does not see it as a variable.

And remember the translate function has the arguments reversed compared to human logic: Translate(Variable, TO, FROM), so you get JOPON, where I guess you would want JAPAN. - The Tranwrd function has the arguments in logic order.

Try this, you get JAPAN in both new variables:

data trns;
	x='JAPON';
	m=translate(x,'A','O');
	n=tranwrd(x,'O','A');
run;

Re: Spelling correction

BrahmanandaRao — Fri, 17 May 2019 09:38:24 GMT

Thank your very much for your support

what if multiple spelling corrections in existing data

data mulspell;
input country$12.;
datalines;
Australia
Newzelandd
Garmany
wasington
netherlands
;
run;

Regards, ANAND

Re: Spelling correction

ErikLund_Jensen — Fri, 17 May 2019 17:33:21 GMT

Hi @BrahmanandaRao

Corrections with string functions will be impossible, because a translation that works for one name will ruin another. I do the following, which is very useful if it is a production job:

1. Make a table with all distinct names selected from source and sorted by name.

2.Export the list to excel.

3. Open the spreadsheet, write the correct name in column B and copy it down to all different occurrences of the same country. Note that each country should have a row with the correct name in col. A. - Save.

4. Read the spreadsheet into a SAS table.

5. Left Join the source table and the correction table on country. Now all names found in the correction table will be translated,

6. Make a table with distinct names where corrected_name = ''.

7. If this table has zero rows, everything is OK. Otherwise you have new countries or new misspellings in your input. Add the names to your spreadsheet and rerun 4-7.

Re: Spelling correction

BrahmanandaRao — Sat, 18 May 2019 06:31:57 GMT

Thank you very much for your valuable advise

Regards

Anand