BookmarkSubscribeRSS Feed
Reeza
Super User
Then either your join condition is incorrect or you need to trim the fields of blanks before concatenating them. Post your code.
14sas
Calcite | Level 5

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
Super User
That's not valid code, there weren't errors in the log?
14sas
Calcite | Level 5
Yes , there are but this is Wat my requirement is .
Reeza
Super User

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
Calcite | Level 5

You can also try this

 

New_Col = Compress(Prefix||Your_col);

14sas
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 3270 views
  • 1 like
  • 5 in conversation