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.
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;
Is this for reporting purposes or do you need a SAS data set like this?
So are you ok with Proc Tabulate or Proc Report?
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;
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;
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;
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 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 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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.