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.
... View more
