Help using Base SAS procedures

Replace letter with number

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Replace letter with number

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


Accepted Solutions
Solution
‎06-05-2013 07:58 AM
Regular Contributor
Posts: 213

Re: Replace letter with number

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;

View solution in original post


All Replies
Solution
‎06-05-2013 07:58 AM
Regular Contributor
Posts: 213

Re: Replace letter with number

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;

Contributor
Posts: 38

Re: Replace letter with number

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

Regular Contributor
Posts: 213

Re: Replace letter with number

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

Contributor
Posts: 38

Re: Replace letter with number

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

Regular Contributor
Posts: 213

Re: Replace letter with number

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

Contributor
Posts: 38

Re: Replace letter with number

HI Ahmed,

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

Thanks Ahmed, everyone.

Fred

Super Contributor
Posts: 282

Re: Replace letter with number

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.

Contributor
Posts: 38

Re: Replace letter with number

Hi Amir,

I only the column with existing codes.

Fred

Super Contributor
Posts: 282

Re: Replace letter with number

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.

Contributor
Posts: 46

Re: Replace letter with number

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?

Contributor
Posts: 38

Re: Replace letter with number

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

Fred

Contributor
Posts: 46

Re: Replace letter with number

Its me Surendar....  not ahmed

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 340 views
  • 0 likes
  • 4 in conversation