## Sum by two groups

Occasional Contributor
Posts: 15

# Sum by two groups

Hello!

I am trying to figure out how to calculate the sum by two variables using proc sql.

This is what I have:

id      date              change

1       1/3/15           1

1       1/3/15           0

1       1/3/15           1

1       2/1/15           -1

2       4/1/10           1

2       4/1/10           -1

2       7/10/10         0

This is what I need:

id      date              all_change    min_change   max_change

1       1/3/15           2                   0                    1

1       2/1/15           -1                 -1                   -1

2       4/1/10           0                  -1                    1

2       7/10/10         0                   0                    0

I tried using this:

Proc sql;
Create table change as
Select id
,date
,sum(change) AS all_change
,min(change) AS min_change
,max(change) AS max_change
From dataset
group by
id and date;
Quit;

Instead of giving me the sum, min, and max by each id's set of dates, it takes the values for all ids. So I get the same sum of all the change rows in the full dataset for all the ids.

Can anyone let me know what I am doing wrong?

Super User
Posts: 23,683

## Re: Sum by two groups

``Group by ID, Date``

No AND

@heretolearn wrote:

Hello!

I am trying to figure out how to calculate the sum by two variables using proc sql.

This is what I have:

id      date              change

1       1/3/15           1

1       1/3/15           0

1       1/3/15           1

1       2/1/15           -1

2       4/1/10           1

2       4/1/10           -1

2       7/10/10         0

This is what I need:

id      date              all_change    min_change   max_change

1       1/3/15           2                   0                    1

1       2/1/15           -1                 -1                   -1

2       4/1/10           0                  -1                    1

2       7/10/10         0                   0                    0

I tried using this:

Proc sql;
Create table change as
Select id
,date
,sum(change) AS all_change
,min(change) AS min_change
,max(change) AS max_change
From dataset
group by
id and date;
Quit;

Instead of giving me the sum, min, and max by each id's set of dates, it takes the values for all ids. So I get the same sum of all the change rows in the full dataset for all the ids.

Can anyone let me know what I am doing wrong?

Discussion stats