One way to do it is with a Full Outer Join keyed on the "value" column, with a calculated column as the output.
For example, imagine these two data sets:
data one;
do id=1 to 10;
value = ranuni(100);
output;
end;
run;
data two;
do id=5 to 15;
value = ranuni(200);
output;
end;
run;
Join them in a query with a Full Outer Join on VALUE (not ID), specify that you want DISTINCT rows, and add this calculated column to the output:
MAX(ONE.VALUE, TWO.VALUE)
The resulting SQL is this:
PROC SQL;
CREATE TABLE WORK.Query_for_TWO AS SELECT DISTINCT (MAX(TWO.value, ONE.value)) AS UniqueValue
FROM WORK.TWO AS TWO
FULL JOIN WORK.ONE AS ONE ON (TWO.value = ONE.value);
QUIT;
If I've misunderstood what you're after, please follow up with another post.
I know that I'm inviting comments from the peanut gallery about how this can be done in two lines of DATA step code; remember, the question was about how to get this done in the query UI
🙂
Chris