Sum by two groups

heretolearn — Fri, 22 Jun 2018 06:04:01 GMT

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?

Re: Sum by two groups

Reeza — Fri, 22 Jun 2018 06:11:31 GMT

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?

topic Re: Sum by two groups in SAS Programming

Sum by two groups

Re: Sum by two groups