Using the SET Statement to combine tables

[Zatere]

Hello, I would like to ask a question on SET statements when used to combine tables.

I have got two tables which I am trying to combine:

Table Global

EmployeeID	Name	Location
1	A	Global
2	B	Global
3	C	Global

Table Local

EmployeeID	Name	Location	Gender
1	A	Europe	M
2	B	Asia	F
3	C	Africa	M

I want to use SET statements:

DATA Combined;
	SET Global Local; 
RUN;

That gives:

EmployeeID	Name	Location	Gender
1	A	Global	M
1	A	Europe
2	B	Global	F
2	B	Asia
3	C	Global	M
3	C	Africa

What I wanted to find out is if I can get the below results still using SET statements:

EmployeeID	Name	Location	Gender
1	A	Global	M
1	A	Europe	M
2	B	Global	F
2	B	Asia	F
3	C	Global	M
3	C	Africa	M

Thanks in advance.

[Kurt_Bremser · Accepted solution]

Try this:

data combined;
set
  local (rename=(gender=_gender))
  global
;
by employeeid;
retain gender;
if first.employeeid then gender = _gender;
drop _gender;
run;

Or, shorter:

data combined;
set
  local
  global
;
by employeeid;
gender = ifc(first.employeeid,gender,lag(gender));
run;

[Zatere]

Thanks a lot! I learnt something new. I was hoping though not to have to sort the data.