topic Re: sum up values across observation by condition in SAS Programming

sum up values across observation by condition

ali_far — Mon, 26 Jun 2017 11:39:41 GMT

Hi all,

I have a big data set with almost 100 milion observation.

ID b c

1 4 1

1 5 1

1 3 1

1 5 2

2 1 2

2 4 2

2 3 1

2 4 1

for each Id I wanted to sum up the values of variable b. I did it using proc sql by group. for exampole id=1 sum= 22

my question is that i want to sum up the values of variable b for each id based on variable c. A table like below

ID b c total_sum wanted result

1 4 1 22 12

1 5 1 22 12

1 3 1 22 12

1 5 2 22 10

2 1 2 12 5

2 4 2 12 5

2 3 1 12 7

2 4 1 12 7

your help wil be apprecited

Ali

Re: sum up values across observation by condition

PeterClemmensen — Mon, 26 Jun 2017 11:45:21 GMT

Like this?

data have;
	input ID$ b c$;
	datalines;
1 4 1
1 5 1
1 3 1
1 5 2
1 5 2
2 1 2
2 4 2
2 3 1
2 4 1
;

proc sql;
	create table want as
	select *
	      ,sum(b) as wanted_sum
	from have
	group by ID, c
	order by ID, c;
quit;

Re: sum up values across observation by condition

ali_far — Mon, 26 Jun 2017 13:00:41 GMT

thanks it worked. i tried before without order statement. is there another way using SAS statement instead of SQL?
Thanks