Search code examples
sqlsasdatastep

intervals of dates - summing

I have one table with dates:

Table1
Date_start  Date_end
01JUL1997   01JUL1998
01JUL1998   01APR1999
01APR1999   01OCT2000
01OCT2000   01JUL2001

and second table where with date and number :

Table2
Date_of_pay           Cash 
01DEC1999:00:00:00  $377.00
01DEC1999:00:00:00  $377.00
01JAN2000:00:00:00  $377.00
01JAN2000:00:00:00  $377.00
01JAN2000:00:00:00  $377.00
01JAN2000:00:00:00  $377.00

I want to make sum of cash in Table2 for each interval in Table1. I do not know how to do it with proc sql neither in data-step.

Thank you for any help.

Solution

  • Please try the following query:

    SELECT
    table1.date_start, table1.date_end,
    SUM(table2.cash) as cash
FROM 
    table1 INNER JOIN table2
    ON  table2.date_of_pay >= table1.date_start
    AND table2.date_of_pay <= table1.date_end
GROUP BY
    table1.date_start, table1.date_end