SAS SQL: Join and Union in Combination to Fill Rows
<p>I'm essentially trying to join on two variables, <code>ID</code> and <code>ACCOUNTNUMBER</code>. Easy. However, the <code>FULL</code> dataset has a row of a common <code>ID</code> but different <code>ACCOUNTNUMBER</code> (note the rename) and <code>CAR</code>. I'd like to merge these in and have the <code>ID</code> retain these values. See the example data below:</p>

<pre><code>DATA FULL;
    LENGTH ID ACCT_FULL CAR $12.;
    INPUT ID $ ACCT_FULL $ CAR $;
    DATALINES;
    A   123 MAZDA
    B   456 FORD
    C   789 CHEVY
    D   777 NISSAN
    ;
RUN;

DATA SUBSET;
    LENGTH ID ACCOUNTNUMBER $12.;
    INPUT ID $ ACCOUNTNUMBER $;
    DATALINES;
    A   123
    B   456
    C   789
    D   012
    ;
RUN;

** THIS QUERY DOES NOT QUITE OUTPUT A DATASET I WANT **;
PROC SQL NOPRINT;
    CREATE TABLE WANT_BAD AS
    SELECT *
    FROM SUBSET AS A
    LEFT JOIN
    FULL(RENAME=(ACCT_FULL=ACCOUNTNUMBER)) AS B
    ON      A.ID = B.ID AND 
            A.ACCOUNTNUMBER = B.ACCOUNTNUMBER;
QUIT;
</code></pre>

<p>Here is a side-by-side comparison of what I currently have versus what I want:</p>

<pre><code>Bad Output:      Want Output:

A   123 MAZDA   |   A   123 MAZDA
B   456 FORD    |   B   456 FORD
C   789 CHEVY   |   C   789 CHEVY
D   012         |   D   012 NISSAN
                |   D   777 NISSAN
</code></pre>

<p>My question is -- can I add some sort of <code>UNION</code> statement to my query to output the data I want? I'd like to do this in SQL rather than data steps because my <code>FULL</code> data in real life is massive, and I do not want to read in, sort, and merge using <code>IN=</code> statements because it will take much longer.</p>