topic Re: Aggregate data and sum in SAS Procedures

Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 08:58:37 GMT

Hello everyone,

I have a table with several informations (see table below):

ID	Code	Date	Amount
1	0	31.01.2020	100
1	1	29.02.2020	80
1	2	31.03.2020	120
1	1	30.04.2020	75
2	2	31.01.2020	200
2	2	29.02.2020	150
2	4	31.03.2020	125
2	0	30.04.2020	230
3	1	31.01.2020	75
3	1	29.02.2020	65
3	3	31.03.2020	80
3	4	30.04.2020	90
4	0	31.01.2020	500
4	0	29.02.2020	350
4	1	31.03.2020	200
4	1	30.04.2020	100

and I want to aggregate the data based on the date and sum it up for each code (in excel it would be done in a pivot table), so that my final table looks like:

Date	Code0	Code1	Code2	Code3	Code4	Total
31.01.2020	600	75	200	0	0	875
29.02.2020	350	145	150	0	0	645
31.03.2020	0	200	120	80	125	525
30.04.2020	230	175	0	0	90	495

So the ID is irrelevant for my final table, I want to calculate the sum over the amount for each Code and month and in the last column a total for each month. If possible I would like to do it with an proc sql rather than a proc summary.

I hope it is clear what I need.

Thank you for your help.

Re: Aggregate data and sum

PeterClemmensen — Fri, 10 Jul 2020 08:59:59 GMT

Is this for reporting purposes or do you need a SAS data set like this?

Re: Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 09:02:22 GMT

It is rather for reporting purpose.

Re: Aggregate data and sum

PeterClemmensen — Fri, 10 Jul 2020 09:03:08 GMT

So are you ok with Proc Tabulate or Proc Report?

Re: Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 09:05:17 GMT

I guess proc tabulate would also work, dont have too much experience with proc report. Is proc sql too long/complicated for this problem?

Re: Aggregate data and sum

PeterClemmensen — Fri, 10 Jul 2020 09:58:03 GMT

How about

data have;
input ID Code Date :anydtdte. Amount;
format date ddmmyy10.;
datalines;
1 0 31.01.2020 100
1 1 29.02.2020 80
1 2 31.03.2020 120
1 1 30.04.2020 75
2 2 31.01.2020 200
2 2 29.02.2020 150
2 4 31.03.2020 125
2 0 30.04.2020 230
3 1 31.01.2020 75
3 1 29.02.2020 65
3 3 31.03.2020 80
3 4 30.04.2020 90
4 0 31.01.2020 500
4 0 29.02.2020 350
4 1 31.03.2020 200
4 1 30.04.2020 100
;

proc format;
   picture p other = '00009' (prefix='Code');
run;

proc tabulate data=have out=want format=8.;
   class Code Date;
   var Amount;
   keylabel sum=' ' all= ' ';
   format code p.;
   table date='', code=''*amount='' all*amount=''*(sum='Total') / box='Date';
run;

Re: Aggregate data and sum

Jagadishkatam — Fri, 10 Jul 2020 10:34:49 GMT

Here is a way to do it by prod sql but later to tranpose it is good to use proc transpose

data have;
input ID Code Date :anydtdte. Amount;
format date ddmmyy10.;
datalines;
1 0 31.01.2020 100
1 1 29.02.2020 80
1 2 31.03.2020 120
1 1 30.04.2020 75
2 2 31.01.2020 200
2 2 29.02.2020 150
2 4 31.03.2020 125
2 0 30.04.2020 230
3 1 31.01.2020 75
3 1 29.02.2020 65
3 3 31.03.2020 80
3 4 30.04.2020 90
4 0 31.01.2020 500
4 0 29.02.2020 350
4 1 31.03.2020 200
4 1 30.04.2020 100
;

proc sql;
create table want as select code, date, sum(amount) as suma from have group by code,date 
union all
select 9 as code, date, sum(amount) as suma from have group by date order by date, code;
quit; 

proc transpose data=want out=want2(rename=(code9=total)) prefix=code;
by date;
id code;
var suma;
run;

Re: Aggregate data and sum

hyo_tree — Fri, 10 Jul 2020 10:36:35 GMT

have a try.

data raw;
input Code Date $ Amount @@ ;
datelines;
0

31.01.2020

100

29.02.2020

31.03.2020

120

30.04.2020

31.01.2020

200

29.02.2020

150

31.03.2020

125

30.04.2020

230

31.01.2020

29.02.2020

31.03.2020

30.04.2020

31.01.2020

500

29.02.2020

350

31.03.2020

200

30.04.2020

100
p;
run;
proc sql;
create table final
select distinct date, total
,sum ( code_0) as code0
,sum ( code_1) as code1
,sum ( code_2) as code2
,sum ( code_3) as code3
,sum ( code_4) as code4
from
(select date, sum(amount),case when code =0 then amount as code_0,
case when code =1 then amount as code_1,
case when code =2 then amount as code_2,
case when code=3 then amount as code_3,
case when code=4 then amount as code_4
from raw
group by date)
group by date;
quit;

Re: Aggregate data and sum

PaigeMiller — Fri, 10 Jul 2020 11:42:04 GMT

You can do this in PROC REPORT, it's pretty simple. I wasn't able to get the "Total" column at the right, and so I'm sure someone (@Ksharp ? @Cynthia_sas ?) out there can guide me on how to get that "Total" column using PROC REPORT.

Here's the rest of the table (without the "total" column at the right) using PROC REPORT

proc report data=have;
    columns date code,amount;
    define date/group;
    define code/across;
    define amount/analysis sum;
run;

Re: Aggregate data and sum

Ksharp — Fri, 10 Jul 2020 12:02:43 GMT

Hi Paige,
For a pivot table , PROC TABULATE is better than REPORT .

Re: Aggregate data and sum

PaigeMiller — Fri, 10 Jul 2020 12:13:04 GMT

@Ksharp wrote:
Hi Paige,
For a pivot table , PROC TABULATE is better than REPORT .

Not if I don't know PROC TABULATE. And I don't really believe that it can't be done in PROC REPORT.

nevertheless, if anyone knows how to do this entirely with PROC REPORT, please advise. I think it's simple, but I just don't know how to do it. If I manipulate the data in PROC SUMMARY before I run PROC REPORT, I get the desired output.

Re: Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 12:10:39 GMT

Thank you very much

Re: Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 12:11:11 GMT

Thanks a lot for the help!

Re: Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 12:13:00 GMT

Thank you, that is also an intersting way to do .

Re: Aggregate data and sum

PaigeMiller — Fri, 10 Jul 2020 12:21:57 GMT

@PaigeMiller wrote:

@Ksharp wrote:
Hi Paige,
For a pivot table , PROC TABULATE is better than REPORT .

Not if I don't know PROC TABULATE. And I don't really believe that it can't be done in PROC REPORT.

nevertheless, if anyone knows how to do this entirely with PROC REPORT, please advise. I think it's simple, but I just don't know how to do it. If I manipulate the data in PROC SUMMARY before I run PROC REPORT, I get the desired output.

Aha! By brute force trying a whole lot of possibilities (instead of the more intelligent method of looking at the documentation), I figured out how to get the Total column on the right. Example using SASHELP.CLASS:

proc report data=sashelp.class;
	columns sex age,height ("Total" height=sum);
	define sex/group;
	define age/across;
	define height/sum;
run;

Re: Aggregate data and sum

Jay_Aguilar — Fri, 10 Jul 2020 12:24:24 GMT

Can you tell me what the Select 9 is doing?

Re: Aggregate data and sum

Ksharp — Fri, 10 Jul 2020 12:31:57 GMT

Paige,

As your wish , but that is not easy, you need change the code according to the data.

data have;
input ID Code Date :anydtdte. Amount;
format date ddmmyy10.;
datalines;
1 0 31.01.2020 100
1 1 29.02.2020 80
1 2 31.03.2020 120
1 1 30.04.2020 75
2 2 31.01.2020 200
2 2 29.02.2020 150
2 4 31.03.2020 125
2 0 30.04.2020 230
3 1 31.01.2020 75
3 1 29.02.2020 65
3 3 31.03.2020 80
3 4 30.04.2020 90
4 0 31.01.2020 500
4 0 29.02.2020 350
4 1 31.03.2020 200
4 1 30.04.2020 100
;


options missing='0';
proc report data=have nowd  out=w;
    columns date code,amount total;
    define date/group;
    define code/across;
    define amount/analysis sum;
	define total/computed 'Total';
	compute total;
     total=sum(_c2_,_c3_,_c4_,_c5_,_c6_);
	endcomp;
run;

Re: Aggregate data and sum

PaigeMiller — Fri, 10 Jul 2020 12:35:13 GMT

Thanks @Ksharp , but the documentation clearly indicates you can put a statistic keyword in the COLUMNS statement. So the explicit calculation via a compute block shouldn't be necessary.

Nevertheless, the code I posted a few minutes ago works only for SUM, I still can't get it to work for MEAN.

Re: Aggregate data and sum

PaigeMiller — Fri, 10 Jul 2020 12:57:19 GMT

@PaigeMiller wrote:

Nevertheless, the code I posted a few minutes ago works only for SUM, I still can't get it to work for MEAN.

Now it works with MEAN (and analogously for any other statistic SUM, P75, etc.)

proc report data=sashelp.class;
    columns sex ("Age" age,height ("Average" height=heightmean));
    define sex/group;
    define age/across ' ';
    define height/analysis mean;
    define heightmean/analysis mean format=6.1;
run;