intervals of dates - summing

Question

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.

Please post what you expect as results, what you've tried so far and ideally your data as a data step but the first two are more important. As posted this is not a valid SO question. — Reeza, Jun 28 '17 at 08:29
`Date_start` and `Date_end` are overlapping if you use `>=` and `<=` like in Jesus Lopez' answer. — zuluk, Jun 28 '17 at 10:59

score 0 · Accepted Answer · answered Jun 28 '17 at 08:42

0

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

answered Jun 28 '17 at 08:42

Jesús López

8,338
7
40
66

You don't make any comment on the possibility that table1 appears to store dates and table2 appears to store datetimes. If this is the case then your answer would not work – Longfish Jun 28 '17 at 09:01
Thank you ,it works! (I add conversion of datetime to date as Longfish mentioned) – mical Jun 28 '17 at 09:10
@Longfish. Yes you are right, I assume dates are the same type. – Jesús López Jun 28 '17 at 12:13

intervals of dates - summing

1 Answers1