SAS PROC SQL - issue with Left join on two character variables
<pre><code>    DataSet: Test1

    Name        Type        Length  Format      Informat    
    RowID       Numeric     8        6.           6.        
    COL2        Character   6       $6.           $6.       
    COL3        Numeric     8    NUMERIC12.      NUMERIC12. 
    DATE        Numeric     8        11.                   11.      
    TIME        Character   8      $CHAR8.             $CHAR8.      
    Amount      Numeric     8                   


  DataSet: Test2

Name                Type        Length  Format      Informat    
RowID               Numeric     8   9.              9.      
COL2                Character   32  $32.             $32.       
COL3                Date        8   DATETIME27.6    DATETIME27.6    
COL4                Character   17  $17.            $17.        
TIME                Character   8   $CHAR8.         $CHAR8.     
COL5                Numeric     8   NUMERIC12.       NUMERIC12. 
AMOUNT              Numeric     8                   
</code></pre>

<p>Sample Data </p>

<pre><code>Test1

RowID    COL2      COL3     DATE         TIME      AMOUNT

3330    123456     123      20110523    14.14.50    2.00
3330    334567     123      20110523    19.13.34    2.00
3330    889789     123      20110523    20.01.11    2.00
3330    45678      1643     20110523    06.53.05    6.00


TEST2

RowID    COL2                                           COL3             COL4                   TIME       COL5    Amount

3330    0010181002233611096xyBC3TLnDkVB7   23MAY2011:19:14:50.000000    20110523 14:14:50    14:14:50      123     2.00                                                             
3330    0010181005005029491mnopqrbT2cySA   24MAY2011:00:13:34.000000    20110523 19:13:34    19:13:34      123     2.00          
3330    001018100222213220332ghijkl63BR1   23MAY2011:11:53:05.000000    20110523 06:53:05    06:53:05      1643    6.00       
3330    00101810021738472682abcdef7vUcte   23MAY2011:13:30:03.000000    20110523 08:30:03    06:53:05      5575    1.00         
</code></pre>

<p>I want to left join Test1 with Test2 on Columns  Test1.COL3=Test2.COL5 and Test1.Time=Test2.TIME   with the desired output to look like</p>

<pre><code> Final

 RowID   COL2      COL3     DATE         TIME      AMOUNT     RowID          COL2                              COL3                               COL4            TIME       COL5    Amount

3330    123456     123      20110523    14.14.50    2.00      3330     0010181002233611096xyBC3TLnDkVB7     23MAY2011:19:14:50.000000       20110523 14:14:50    14:14:50      123     2.00   
3330    334567     123      20110523    19.13.34    2.00      3330     0010181005005029491mnopqrbT2cySA     24MAY2011:00:13:34.000000       20110523 19:13:34    19:13:34      123     2.00  
3330    889789     123      20110523    20.01.11    2.00         .           .                  .             .          .      .     .
3330    45678      1643     20110523    06.53.05    6.00      3330     001018100222213220332ghijkl63BR1     23MAY2011:11:53:05.000000    20110523 06:53:05    06:53:05      1643       6.00
</code></pre>

<p>I'm running the below code is SAS</p>

<pre><code>  proc sql;
  create table final as
  select * from 
  (
  (select * from Test1) A
  left join
  (select * from Test2) B
  on Test1.COL3=Test2.COL5 and Test1.Time=Test2.TIME
  )  
  quit;
</code></pre>

<p>and Im not getting the desired output even though TIME column in both the datasets are of same length, Format and Informat</p>

<p>I'm getting the results as</p>

<pre><code>  Final

  RowID  COL2      COL3     DATE         TIME      AMOUNT     RowID    COL2                 COL3             COL4            TIME    COL5    Amount

  3330  123456     123      20110523    14.14.50    2.00         .           .                 .              .           .     .    .
  3330  334567     123      20110523    19.13.34    2.00         .           .                 .              .           .     .     .
  3330  889789     123      20110523    20.01.11    2.00         .           .                 .              .          .      .     .
   3330 45678      1643     20110523    06.53.05    6.00         .           .                 .              .           .      .     .

I do not understand what is wrong. 
</code></pre>