Building Union/Merge Queries

[deleted_user]

I have to datasets with two variables (ID, Value). Each observation in each table is unique. How to I get enterprise guide to build the union query to form one dataset with one variable (value). I want to end up with one dataset listing all unique value varibles.

I know how to write a proc sql, but I want the gui to do it.

Thank You
Eric

[ChrisHemedinger]

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