title: SAS: Merge or join and retain all records while filling missing
body: <p>I'm essentially splitting a dataset into two (those that have an ID and those that are missing ID), and merging the missing back into the non-missing by a set of match keys to help fill the ID. I have five total records below, and the final dataset needs to maintain all five records. Below is an example:</p>
  
  <pre><code>DATA TEST;
      LENGTH ID MKEY $12.;
      INPUT ID $ MKEY $;
      DATALINES;
      .   M123
      .   M456
      A   M123
      B   M456
      C   M789
      ;
  RUN;
  
  DATA MPOOL CPOOL; SET TEST;
      IF ID IN ("","0") THEN DO;
          MISS_ID = 1;
          OUTPUT MPOOL;
      END;
      ELSE DO;
          MISS_ID = 0; 
          OUTPUT CPOOL;
      END;
  RUN;
  </code></pre>
  
  <p>So we end up merging the missing data <code>MPOOL</code>:</p>
  
  <pre><code>.   M123
  .   M456
  </code></pre>
  
  <p>with the non-missing data <code>CPOOL</code>:</p>
  
  <pre><code>A   M123
  B   M456
  C   M789
  </code></pre>
  
  <p>Merging only gives me three records back, but I need to maintain all records and fill the missing (note that through the MKEY, we should be to link A and B records to missing IDs) as shown below:</p>
  
  <pre><code>A   M123
  A   M123
  B   M456
  B   M456
  C   M789
  </code></pre>
  
  <p>What kind of <code>SQL JOIN</code> would allow me to keep all records and fill the missing with those that have been successfully joined? Seems feasible but <code>MERGE</code> with this data never retains all records. I know I can lag/retain/fill in this example, but the big data I'm working with requires merging/joining due to other factors. </p>