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
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;
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;
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
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
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
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
HI Ahmed,
Ok I overcame the probem by just changing Best. to $Char.
Thanks Ahmed, everyone.
Fred
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.
Hi Amir,
I only the column with existing codes.
Fred
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.
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?
Thank you AhmedAl-Attar, simple and worked like a charm.
Fred
Its me Surendar.... not ahmed
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.