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.
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;
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;
Thanks a lot! I learnt something new. I was hoping though not to have to sort the data.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.