topic Re: Replace letter with number in SAS Procedures

Replace letter with number

fred_major — Wed, 05 Jun 2013 11:43:05 GMT

Hi,

I have a list of employee codes such as the list below.

How can I change the first character if it is a letter to the approprite number?

Existing Required

A566545 1566545

P001857 3001857

N401225 2401225

Thanks

Fred

Re: Replace letter with number

AhmedAl_Attar — Wed, 05 Jun 2013 11:58:28 GMT

Use the Translate function.

data orig;

length empCode $7;

input empCode $;

datalines;

A566545

P001857

N401225

;

run;

data orig_mod;

Set orig;

length empCode_n 8;

empCode_n = input(translate(empCode,'1','A','3','P','2','N'),best.);

run;

Re: Replace letter with number

Amir — Wed, 05 Jun 2013 11:59:31 GMT

Hi,

Do you already have both columns of data available to you to refer back to or are there some rules to be applied at run-time to convert the letter to a number and if so what are the rules?

Regards,

Amir.

Re: Replace letter with number

fred_major — Wed, 05 Jun 2013 12:02:04 GMT

Hi Amir,

I only the column with existing codes.

Fred

Re: Replace letter with number

suraestar — Wed, 05 Jun 2013 12:07:43 GMT

Hi..

Here is the answer for your requirement,

data _null_;

empid='A566545';

if anyalpha(substr(empid,1,1)) NE 0 then substr(empid,1,1)='1';

put empid=;

run;

Let me know if this is what you expect?

Re: Replace letter with number

fred_major — Wed, 05 Jun 2013 12:20:15 GMT

Thank you AhmedAl-Attar, simple and worked like a charm.

Fred

Re: Replace letter with number

suraestar — Wed, 05 Jun 2013 12:24:48 GMT

Its me Surendar.... not ahmed

Re: Replace letter with number

Amir — Wed, 05 Jun 2013 12:36:26 GMT

Hi,

An alternative is to use formats so if your input data changes your program can stay the same:

data cntlin;

retain fmtname '$empfix';

input start $

label $

;

datalines;

A566545 1566545

P001857 3001857

N401225 2401225

;

proc format cntlin=cntlin;

run;

data _null_;

input emp $;

put emp emp empfix.;

datalines;

A566545

P001857

N401225

1234567

X234567

;

Regards,

Amir.

Re: Replace letter with number

fred_major — Wed, 05 Jun 2013 14:04:12 GMT

Hi Ahmed,

I had one unexpected result and am hoping you can suggest solution.

Some of the existing codes start with EX and this is causing required result to return null.

Existing Required

EX77777 1566545

EX88888 3001857

EX99999 2401225

Thanks

Fred

Re: Replace letter with number

AhmedAl_Attar — Wed, 05 Jun 2013 14:55:08 GMT

Fred,

If you were only interested in replace characters with numbers, then modify the translate function clause by adding additional value substitution for E and X.

data orig_mod;

Set orig;

length empCode_n 8;

empCode_n = input(translate(empCode,'1','A','3','P','2','N','<some number>','E','<some number>','X'),best.);

run;

If you want to replace the entire value from EX77777 to 1566545, then I would recommend using the format approach, as suggested by Amir.

Ahmed

Re: Replace letter with number

fred_major — Wed, 05 Jun 2013 14:59:31 GMT

No actually I don't want it to do anything when the nip starts with EX but what is happening is that it is being converted to . or null

thanks

Fred

Re: Replace letter with number

AhmedAl_Attar — Wed, 05 Jun 2013 15:35:20 GMT

Fred,

Then Add a condition to test for 'EX' as a starting two chars, if true then leave as is, otherwise use the translate function!

Ahmed

Re: Replace letter with number

fred_major — Wed, 05 Jun 2013 16:57:25 GMT

HI Ahmed,

Ok I overcame the probem by just changing Best. to $Char.

Thanks Ahmed, everyone.

Fred