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;
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.
You can also try this
New_Col = Compress(Prefix||Your_col);
this wont be a new column ,just whenever your relationship_cd is dependent ,emp_id has to be prefix + emp_id .
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.