how to add prefix to a column

[Reeza]

Then either your join condition is incorrect or you need to trim the fields of blanks before concatenating them. Post your code.

[14sas]

This is my program:

proc sql;

create table TEST as 

where relationship_cd ='s' then employee_id = "S"II ''II (employee_id)

else trim (employee_id)

end as employee_id

from master_file

;

quit;

[Reeza]

That's not valid code, there weren't errors in the log?

[14sas]

Yes , there are but this is Wat my requirement is .

[Reeza]

Based on the data you provided, I provided code that meets your stated requirements. Your code doesn't have a join condition or valid SQL. I'm not sure how I can help you beyond this.

Did my example work for you? Is there some part you did't understand?

/*Generate sample data*/
data have;
infile cards dsd truncover;
informat relationshp_cd $12.;
input  order_var emp_id $  relationshp_cd $  uniq_id $;
cards;
1, 1234, employee,  a123401
2, 3456, employee,  a987601
3,    , dependent, a987602
;
run;

/*Create Spousal ID*/
proc sql;
create table want as
select a.order_var,
       coalesce(a.emp_id, cats("S", b.emp_id)) as emp_id,
		a.relationshp_cd,
		a.uniq_id
from have as a
left join have as b
/*this is the condition you need to modify to meet your joining criteria*/
on substr(a.uniq_id, 1, 5)=substr(b.uniq_id, 1, 5)
and b.relationshp_cd='employee'
order by a.order_var;
quit;

proc print data=want;
run;

Essentially, you need to join the data to itself on the criteria that the first portion of the unique ID match. I think they use the last two or three digits to identify family members within the same family.  

[Tanmay]

You can also try this

New_Col = Compress(Prefix||Your_col);

[14sas]

this wont be a new column ,just whenever your relationship_cd is dependent ,emp_id has to be prefix + emp_id .