I have two dataset similar to the examples below, i wanted to read only observations that are in both data.
Dataset one Dataset two
ID A B ID A B
10 1 2 10 0 4
20 3 4 30 5 2
30 5 6 40 7 3
I want the output that looks like this
ID A B
10 1 2
10 0 4
30 5 6
30 5 2
data one;
input ID A B ;
cards;
10 1 2
20 3 4
30 5 6
;
data two;
input ID A B ;
cards;
10 0 4
30 5 2
40 7 3
;
proc sql;
create table want as
select * from one where id in (select id from two)
union all
select * from two where id in (select id from one)
order by id;
quit;
What have you tried?
You need to interleave both datasets, outputting only those observations for which first.id and last.id are not true / one at the same time.
data want;
set one two;
by ID;
if not (first.id and last.id);
run;
data one;
input ID A B ;
cards;
10 1 2
20 3 4
30 5 6
;
data two;
input ID A B ;
cards;
10 0 4
30 5 2
40 7 3
;
proc sql;
create table want as
select * from one where id in (select id from two)
union all
select * from two where id in (select id from one)
order by id;
quit;
Is it possible that there are two or more observations for a single ID in one dataset?
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!
Still thinking about your presentation idea? The submission deadline has been extended to Friday, Nov. 14, at 11:59 p.m. ET.
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.
Ready to level-up your skills? Choose your own adventure.