DATA Step, Macro, Functions and more

sum up values across observation by condition

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

sum up values across observation by condition

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

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

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


Accepted Solutions
Solution
‎06-26-2017 09:00 AM
PROC Star
Posts: 552

Re: sum up values across observation by condition

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎06-26-2017 09:00 AM
PROC Star
Posts: 552

Re: sum up values across observation by condition

[ Edited ]

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;
Occasional Contributor
Posts: 9

Re: sum up values across observation by condition

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 108 views
  • 0 likes
  • 2 in conversation