group by function with sum in select clause

mudassir — Thu, 20 Aug 2020 17:16:54 GMT

I have a table below, I am trying to summing up the amount group by month variable.

But I am not getting required result with my query.

Please can some one help me to get proper output.

Customer	Month1	Type	Amount
A1	Jan-04	JEWELLERY	188544.6983
A1	Jan-04	PETRO	277066.25
A1	Jan-04	CLOTHES	131813.031
A1	Jan-04	FOOD	148244.2563
A1	Jan-04	TRAIN TICKET	114180.6856
A1	Jan-04	TRAIN TICKET	432552.6174
A1	Feb-04	BUS TICKET	349329.4567
A1	May-04	BUS TICKET	373086.1377
A1	Jan-05	CAMERA	132804.9577
A1	Feb-05	SANDALS	306648.4663
A1	Feb-05	CAR	452239.2284
A1	Feb-05	FOOD	372511.4839
A1	Feb-05	CLOTHES	201655.2137
A1	Aug-05	MOVIE TICKET	200904.7457
A1	Nov-05	PETRO	412062.8038
A1	Apr-06	AIR TICKET	263346.9597
A1	Apr-06	TRAIN TICKET	491531.3638
A1	Oct-06	RENTAL	152924.9167

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

73 proc sql;

74 select customer,month1,round(sum(amount),.01) as amt from capstone.spend

75 where customer ='A1'

76 group by customer,month1;

77 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.18 seconds

cpu time 0.17 seconds

79 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

I am getting below output.

Costomer	Month	amt
A1	JAN2004	277066.3
A1	JAN2004	302725.4
A1	JAN2004	131813
A1	JAN2004	580796.9
A1	FEB2004	349329.5
A1	MAY2004	373086.1
A1	JAN2005	132805
A1	FEB2005	452239.2
A1	FEB2005	372511.5
A1	FEB2005	508303.7
A1	AUG2005	200904.8
A1	NOV2005	412062.8
A1	APR2006	491531.4
A1	APR2006	263347
A1	OCT2006	152924.9

But i want only 9 rows in output.

Re: group by function with sum in select clause

Reeza — Thu, 20 Aug 2020 17:22:38 GMT

Month is probably a data variable with a format applied. SQL doesn't aggregate at the formatted values, but PROC MEANS will. Can you use one of the summary procs instead?
Otherwise, you can also apply the format to your code.
Not sure if you need the calculated key word in the GROUP BY or not....

Something like the following works but I would also highly recommend PROC MEANS instead.

proc sql;
select customer, put(month1, monyy5.) as month, round(sum(amount),.01) as amt from capstone.spend
where customer ='A1'
group by customer, calculated month;
quit;

proc means data=capstone.spend SUM NWAY maxdec=1 STACKODS;
where customer='A1';
CLASS Customer Month;
var amt;
ods output summary = want;
run;

topic group by function with sum in select clause in New SAS User

group by function with sum in select clause

Re: group by function with sum in select clause