BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ali_far
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20

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;
ali_far
Obsidian | Level 7
thanks it worked. i tried before without order statement. is there another way using SAS statement instead of SQL?
Thanks

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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