title: SAS Proc SQL get records with nearest date to a specific date
body: <p>I have 2 tables as below:</p>
  
  <p>Table 1, a user listing table:</p>
  
  <pre><code>Year  Month Id Type 
  2010  3     1  A
  2010  5     2  B
  2010  10    1  A
  2010  12    1  A
  </code></pre>
  
  <p>Table 2 describes user promotion history:</p>
  
  <pre><code>Promote Date Id
  2/20/2010    1
  5/20/2010    1     (4/2010 the user got demoted, and after 1 month he got promote again)
  </code></pre>
  
  <p>From these 2 tables, I need to produce a result table that likes the table 1, but add a column which classifies a user with type A has been promoted in the last 3 months or more than 3 months at a specific date. For example, the results would be: </p>
  
  <pre><code>Year  Month Id | Duration
  2010  3     1  | A &lt; 3 months
  2010  10    1  | A &gt; 3 months
  2010  12    1  | A &gt; 3 months    
  </code></pre>
  
  <p>General idea would be:</p>
  
  <ul>
  <li>I need to convert the month column and year column from table 1 to a
  date format like 3/2010</li>
  <li>subtract the new converted value with the nearest promote date to the
  above date (2/2010) to get the numbers of days that user has been
  promoted</li>
  <li>compare to 90 days to classifies his promoted duration</li>
  </ul>
  
  <p>There are 2 issues that I'm currently stuck with.</p>
  
  <p>I don't know the best way to convert the month column and year column to month/year date format.</p>
  
  <p>Assumed that I already converted month/year column from table1, I use the Max function to get the nearest date from table2. As far as I know, the max function is not good for the performance, so is there any other solution instead of using max? In mysql, it's easy to solve by using Limit 1, but SAS proc-sql does not support Limit. Is there any equivalent to limit in proc-sql?  Below is the code that I'm currently thinking of.</p>
  
  <pre><code>PROC SQL;
  Create table Result as SELECT table1.Year, table1.Month, table1.Code, 
  (Case When table1.Type = "B" then "B"
  When table1.Type = "A" AND (table1.Date - (Select MAX(table2.Date) From table2 Where table2.Date &lt;= table1.Date AND table2.Id = table1.Id ) &lt; 90) THEN "A &lt; 3 months"
  When table1.Type = "A" AND (table1.Date - (Select MAX(table2.Date) From table2 Where table2.Date &lt;= table1.Date AND table2.Id = table1.Id ) &gt;= 90) THEN "A &gt; 3 months"
  When table1.Type = "C" then "C"
  end) as NewType
  From table1
  LEFT JOIN
  // .... 
  ;
  QUIT;
  </code></pre>
  
  <p>As you can see, I need to left join the table1 with others tables so I use the sub query, which is also a bad performance too, but I don't know if there are any other way. Help and advice are appreciated.</p>