DATA Step, Macro, Functions and more

Sum by two groups

Reply
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

Posted in reply to heretolearn
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?


 

Ask a Question
Discussion stats
  • 1 reply
  • 64 views
  • 0 likes
  • 2 in conversation