creating cumulative totals

[ambadi007]

Create the ORDERS dataset and calculate the cumulative total of Total_retail_price for each month.

As the month changes, the cumulative total should start from the first date of each month again.

data orders;

length order_date $ 10;

input order_date$ id price;

cards;

02JAN2007      1241054779          195.60

03JAN2007      1241063739          160.80

run;

[Kurt_Bremser]

proc sql;
create table want as
select
  month(order_date) as month,
  sum(total_retail_price) as sum_retail
from orders
group by calculated month;
quit;

Note that order_date has to be a proper SAS date column. DO NEVER store dates as character.

[Jagadishkatam]

please try

data orders;
input order_date:date9. order_id Total_retail_price;
month=month(order_date);
format order_date date9.;
cards;
02JAN2007 1241054779 195.60
03JAN2007 1241063739 160.80
04JAN2007 1241066216 306.20
06JAN2007 1241086052 37.80
13JAN2007 1241147641 362.60
23JAN2007 1241235281 72.60
24JAN2007 1241244297 258.20
24JAN2007 1241244297 81.20
24JAN2007 1241244297 358.20
25JAN2007 1241263172 102.40
25JAN2007 1241263172 113.20
28JAN2007 1241286432 174.40
29JAN2007 1241298131 37.40
05FEB2007 1241359997 117.60
07FEB2007 1241371145 656.60
07FEB2007 1241371145 129.00
09FEB2007 1241390440 36.20
16FEB2007 1241461856 29.40
28FEB2007 1241561055 192.00
06MAR2007 1241623505 46.90
09MAR2007 1241645664 150.10
09MAR2007 1241652707 140.70
13MAR2007 1241686210 32.60
16MAR2007 1241715610 16.90
18MAR2007 1241731828 22.70
18MAR2007 1241731828 41.50
25MAR2007 1241789227 121.00
05APR2007 1241895594 24.400
05APR2007 1241895594 13.50
07APR2007 1241909303 107.30
09APR2007 1241930625 58.70
09APR2007 1241930625 56.30
09APR2007 1241930625 90.00
15APR2007 1241977403 19.10
18APR2007 1242012259 231.60
18APR2007 1242012259 19.20
18APR2007 1242012269 142.40
18APR2007 1242012269 73.80
21APR2007 1242035131 147.10
25APR2007 1242076538 57.30
01MAY2007 1242130888 160.50
02MAY2007 1242140006 31.40
02MAY2007 1242140009 16.40
02MAY2007 1242140009 192.40
03MAY2007 1242149082 14.30
03MAY2007 1242149082 56.80
04MAY2007 1242159212 46.60
;

run;

proc sort data=orders;
by month order_date;
run;

data want;
set orders;
by  month order_date;
retain cum_sum;
if first.month then cum_sum=Total_retail_price;
else cum_sum+Total_retail_price;
run;