BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fred_major
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
AhmedAl_Attar
Rhodochrosite | Level 12

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

12 REPLIES 12
AhmedAl_Attar
Rhodochrosite | Level 12

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;

fred_major
Calcite | Level 5

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

AhmedAl_Attar
Rhodochrosite | Level 12

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

fred_major
Calcite | Level 5

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

AhmedAl_Attar
Rhodochrosite | Level 12

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

fred_major
Calcite | Level 5

HI Ahmed,

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

Thanks Ahmed, everyone.

Fred

Amir
PROC Star

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.

fred_major
Calcite | Level 5

Hi Amir,

I only the column with existing codes.

Fred

Amir
PROC Star

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.

suraestar
Calcite | Level 5

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?

fred_major
Calcite | Level 5

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

Fred

suraestar
Calcite | Level 5

Its me Surendar....  not ahmed

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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