title: SAS: how to avoid multiple left joins?
body: <p>I have a dataset like this (just with many more accounts and thus observations)</p>
  
  <pre><code>data dataset;
  input account_id month date9. default_flag;
  format month date9.;
  datalines;
  22 31JAN2004 0
  22 29FEB2004 0
  22 31MAR2004 0
  22 30APR2004 0
  22 31MAY2004 0
  22 30JUN2004 0
  22 31JUL2004 0
  22 31AUG2004 0
  22 30SEP2004 0
  22 31OCT2004 0
  22 30NOV2004 0
  22 31DEC2004 0
  22 31JAN2005 0
  22 28FEB2005 0
  22 31MAR2005 0
  22 30APR2005 0
  22 31MAY2005 1
  ;
  run;
  </code></pre>
  
  <p><code>default_flag</code> variable denotes whether the account is in default (<code>default_flag=1</code>) or not (<code>default_flag=0</code>). </p>
  
  <p>I want to create an X-months forward default flag variable <code>defXMON_flag</code> which is equal to 1 if the account enters default in the following X months and there are at least X months of observations. If the account enters default, but there are less than X months of observations, then the variable will be equal to 3.</p>
  
  <p>I need to create several such variables, for several different X. Let's say I want to create two such variables - one for X=12 months (<code>def12MON_flag</code>) and one for X=15 months (<code>def15MON_flag</code>).</p>
  
  <p>I came up with the following code which produces the results I want:</p>
  
  <pre><code>proc sql noprint;
              create table defaults as
               select a.account_id, a.month, a.default_flag, 
                      sum(b.default_flag) as nr_defaults_12 format = comma17.,
                      count(distinct b.month) as nr_months_12,
                      case when a.default_flag = 0 
                                and coalesce(calculated nr_defaults_12, 0) eq 0 
                                and coalesce(calculated nr_months_12, 0) = 12 then 0 
                           when a.default_flag = 0 
                                and coalesce(calculated nr_defaults_12, 0) ne 0 
                                and coalesce(calculated nr_months_12, 0) = 12 then 1
                           when a.default_flag = 0 
                                and coalesce(calculated nr_defaults_12, 0) eq 0 
                                and coalesce(calculated nr_months_12, 0) ne 12 then 2 
                          when a.default_flag = 0 
                               and coalesce(calculated nr_defaults_12, 0) ne 0 
                               and coalesce(calculated nr_months_12, 0) ne 12 then 3
                          else . 
                      end as def12MON_flag,
  
                      sum(c.default_flag) as nr_defaults_15 format = comma17.,
                      count(distinct c.month) as nr_months_15,
                      case when a.default_flag = 0 
                                and coalesce(calculated nr_defaults_15, 0) eq 0 
                                and coalesce(calculated nr_months_15, 0) = 15 then 0 
                           when a.default_flag = 0 
                                and coalesce(calculated nr_defaults_15, 0) ne 0 
                                and coalesce(calculated nr_months_15, 0) = 15 then 1
                           when a.default_flag = 0 
                                and coalesce(calculated nr_defaults_15, 0) eq 0 
                                and coalesce(calculated nr_months_15, 0) ne 15 then 2 
                          when a.default_flag = 0 
                               and coalesce(calculated nr_defaults_15, 0) ne 0 
                               and coalesce(calculated nr_months_15, 0) ne 15 then 3
                          else . 
                      end as def15MON_flag
  
                  from ( select account_id, month, default_flag from dataset where default_flag = 0) a
                       left outer join ( select account_id, default_flag, month from dataset ) b
                          on 1=1 
                              and a.account_id = b.account_id 
                              and b.month between intnx('month', a.month, 1, 'end') 
                                                          and intnx('month', a.month, 12, 'end')
  
                       left outer join ( select account_id, default_flag, month from dataset ) c
                          on 1=1 
                              and a.account_id = c.account_id 
                              and c.month between intnx('month', a.month, 1, 'end') 
                                                          and intnx('month', a.month, 15, 'end')
                  group by a.account_id, a.month, a.default_flag 
                  order by a.account_id, a.month;
  quit;
  run;
  </code></pre>
  
  <p>This is my end result - not important part, the important part is the creation of table <code>defaults</code> which I need to optimize.</p>
  
  <pre><code>    PROC SQL;
      CREATE TABLE RESULT AS
      SELECT T.*, S.DEF12MON_FLAG, S.DEF15MON_FLAG
      FROM DATASET T
      LEFT JOIN DEFAULTS S 
      ON T.ACCOUNT_ID = S.ACCOUNT_ID
      AND T.MONTH = S.MONTH
      ORDER BY ACCOUNT_ID, MONTH
      ;
  </code></pre>
  
  <p>However, the problem with this code is that it uses two left joins of the huge dataset when creating <code>defaults</code> table (in reality, I would need to use even more left joins for more <code>defXMON_flag</code> variables) and it is not feasible to run it due to the size of those datasets. Is it possible to get the same result without those left joins? Can you please suggest a more efficient way how to calculate <code>def12MON_flag</code> and <code>def15MON_flag</code> with the same result as this code produces? </p>