BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jay_Aguilar
Calcite | Level 5

Hello everyone,

 

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

IDCodeDateAmount
1031.01.2020100
1129.02.202080
1231.03.2020120
1130.04.202075
2231.01.2020200
2229.02.2020150
2431.03.2020125
2030.04.2020230
3131.01.202075
3129.02.202065
3331.03.202080
3430.04.202090
4031.01.2020500
4029.02.2020350
4131.03.2020200
4130.04.2020100

 

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:

DateCode0Code1Code2Code3Code4Total
31.01.20206007520000875
29.02.202035014515000645
31.03.2020020012080125525
30.04.20202301750090495

 

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

18 REPLIES 18
PeterClemmensen
Tourmaline | Level 20

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

Jay_Aguilar
Calcite | Level 5
It is rather for reporting purpose.
PeterClemmensen
Tourmaline | Level 20

So are you ok with Proc Tabulate or Proc Report?

Jay_Aguilar
Calcite | Level 5
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
Tourmaline | Level 20

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
Calcite | Level 5
Thank you very much
Jagadishkatam
Amethyst | Level 16

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;

 

Thanks,
Jag
Jay_Aguilar
Calcite | Level 5
Thanks a lot for the help!
Jay_Aguilar
Calcite | Level 5
Can you tell me what the Select 9 is doing?
hyo_tree
Fluorite | Level 6

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;

Don’t stop pursuing to be a better version of yourself, even if only a little change
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Ksharp
Super User
Hi Paige,
For a pivot table , PROC TABULATE is better than REPORT .
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
PaigeMiller
Diamond | Level 26

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 18 replies
  • 2237 views
  • 2 likes
  • 6 in conversation