Aggregate data and sum

[Jay_Aguilar]

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.

[PeterClemmensen]

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

[Jay_Aguilar]

It is rather for reporting purpose.

[PeterClemmensen]

So are you ok with Proc Tabulate or Proc Report?

[Jay_Aguilar]

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

[PeterClemmensen · Accepted solution]

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;

[Jay_Aguilar]

Thank you very much

[Jagadishkatam]

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;

[Jay_Aguilar]

Thanks a lot for the help!

[Jay_Aguilar]

Can you tell me what the Select 9 is doing?

[hyo_tree]

have a try.

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

31.01.2020

100

1

29.02.2020

80

2

31.03.2020

120

1

30.04.2020

75

2

31.01.2020

200

2

29.02.2020

150

4

31.03.2020

125

0

30.04.2020

230

1

31.01.2020

75

1

29.02.2020

65

3

31.03.2020

80

4

30.04.2020

90

0

31.01.2020

500

0

29.02.2020

350

1

31.03.2020

200

1

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;

[PaigeMiller]

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;

[Ksharp]

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

[PaigeMiller]

---

@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.

[PaigeMiller]

---

@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;