Creating userid from full name

[Solly7]

Hi ,

I need help in created a new column named 'userid' from usersname column

data Users;
input Users $20.;
datalines;
sol sebela

jack mabaso

ally maima

marry anne

jin sol

;
run;

data have;
input policy_no risk contents motor home cycle username $20.;
datalines;
1 1 0 1 0 0  sol sebela
2 0 0 0 0 1  jack mabaso
3 0 1 1 2 1  ally maima
4 1 0 0 0 0  marry anne
5 1 0 0 0 1  jin sol
6 0 1 1 2 0  sin bad
7 1 0 0 2 1  ole man
8 1 2 0 1 0  kim kad
;

proc sql;

      create table data as

select a.* from have as a

where a.username in (select users from users)

;quit;

                                                                        DATA WANT

policy_no	risk	contents	motor	home	cycle	userid	username
1	1	0	1	0	0	ssebela	sol sebela
2	0	0	0	0	1	jmabaso	jack mabaso
3	0	1	1	2	1	amaima	ally maima
4	1	0	0	0	0	manne	marry anne
5	1	0	0	0	1	jsol	jin sol

[japelin]

how about this.

data want;
  set have;
  length userid $20;
  userid=substr(scan(username,1),1,1)||scan(username,2);
run;

[Solly7]

Hi, thanks for the solution..but i need a solution where by ill be able to change the username manually into the userid that i want..check below

Apologies i ddnt mention it from the initial description

data QA_summary;
set Qa_summary_fix;
if username= 'sol sebela' then userid = 'ssebela1';
if username= 'jack mabaso' then userid = 'jmabaso';
run;

[Kurt_Bremser · Accepted solution]

The second IF is not necessary, as that (I assume default) constellation is handled by @japelin 's solution. Just add your first IF to the code that @japelin provided, and handle similar extraordinary situations the same way.

Setting everything manually is a human task, not suited for programming.