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?
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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.