title: Counting the Number of Days an Entry is Active within a particular month using a Date Range in Proc SQL
body: <p>I am earning some data over a period of time but have hit a wall at figuring out some specifics.</p>
  <p>For example I have a large number of rows of data that have an activity <code>START</code> and <code>STOP</code> date. I have performed a cartesian product to join my particular rows of data to that particular month if one of three cases occurs:</p>
  <ol>
  <li>The Item Activity START date occurs before the first day of that particular month and that item's activity stop date occurs after the first day of that month. <code>(ITEM_START&lt;= CalendarMonthStart and ITEM_STOP&gt;= CalendarMonthStart)</code></li>
  <li>The Item Activity START date occurs after the first day of that particular month and that item's activity STOP date occurs before the last day of that particular month (activity is within the single month).<code>(ITEM_START&gt;= CalendarMonthStart and ITEM_STOP&gt;= CalendarMonthEnd)</code></li>
  <li>The Item Activity START date occurs before the last day of that particular month and STOPs after the last day of that particular month.<code>(ITEM_START&lt;= CalendarMonthEnd and ITEM_STOP&gt;= CalendarMonthEnd)</code></li>
  </ol>
  <p>An example of my proc sql query:</p>
  <pre><code>proc sql;
  create table  earned_activity as
  
  select
        a.ITEM_START,
        a.ITEM_STOP,
        b.MonthName,
        b.CalendarMonthStart,
        b.CalendarMonthEnd
  /* Need to do something here */
  from item_activity as a
  left join calendar_table as b on 
            (ITEM_START&lt;= CalendarMonthStart and ITEM_STOP&gt;= CalendarMonthStart) or
            (ITEM_START&gt;= CalendarMonthStart and ITEM_STOP&gt;= CalendarMonthEnd) or
            (ITEM_START&lt;= CalendarMonthEnd and ITEM_STOP&gt;= CalendarMonthEnd)
  
  ;
  quit;
  </code></pre>
  <p>After these three cases are joined to my data I have some example data like below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ITEM_START</th>
  <th>ITEM_STOP</th>
  <th>MonthName</th>
  <th>CalendarMonthStart</th>
  <th>CalendarMonthEnd</th>
  <th>num_days_active</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-01-06</td>
  <td>2021-03-06</td>
  <td>Jan</td>
  <td>2021-01-01</td>
  <td>2021-01-31</td>
  <td>25</td>
  </tr>
  <tr>
  <td>2021-01-06</td>
  <td>2021-03-06</td>
  <td>Feb</td>
  <td>2021-02-01</td>
  <td>2021-02-28</td>
  <td>28</td>
  </tr>
  <tr>
  <td>2021-01-06</td>
  <td>2021-03-06</td>
  <td>Mar</td>
  <td>2021-03-01</td>
  <td>2021-03-31</td>
  <td>6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>As you can see, with the cartesian product I have an item with activity in three different calendar months. I would like to find a way to get the # of days that item existed in each of these months. My first idea would be to perform a cartesian product on each individual days of the month and somehow tally up if that day was active but I believe that might get massive and cumbersome quite quickly. Is there a good approach to performing this type of operation?</p>
  <p>Thank you.</p>