Proc sql sum for each month

Question

I have a table with dates and prices:

28/01/2016  100
04/02/2016  200
16/04/2016  100
23/04/2016  150
07/05/2016  150
14/05/2016  200
.
.
01/12/2017  80
08/12/2017  200

I would like to get a sum based on the month and then year, as follows:

01/2016  100
02/2016  200
04/2016  250
05/2016  350
.
.
12/2017  280

The date is currently in DDMMYY10. format.

*"The date is currently in DDMMYY10"* If you're using SQL Server, that means you're storing your dates as a `varchar` and not a strongly typed date and time data type; that is a design flaw you *really* need to fix. — Thom A, Jun 03 '21 at 11:59
Does this help: [How to group by month from Date field using sql](https://stackoverflow.com/q/14565788/1048425) — GarethD, Jun 03 '21 at 12:06
*"I am using SAS for this"* Then please ensure you tag correctly and not add tags that aren't related to the technology you're using; it can make things very confusing for those who you are asking for help from. — Thom A, Jun 03 '21 at 12:22

score 0 · Answer 1 · answered Jun 03 '21 at 15:53

Convert any individual date value within a month to a the same value and group by that. For example by using INTNX() function.

select intnx('month',date,'b') as month format=yymm7.
     , sum(price) as total_price
from have
group by month

Or convert it to a string using PUT() function.

select put(date,yymm7.) as month 
     , sum(price) as total_price
from have
group by month

1 Answers1