DATA Step, Macro, Functions and more

combinations sum with in the group

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

combinations sum with in the group

Hi I have the data like below. I want to calculate the total with in the group without considering that corresponding value.

if you take G3: for t8 after removing t8 value then the group total is : 1250,  like that i want all the combinations sum.

Is there any macro required here? can you please give some thoughts how can we proceed on this task. Thanks in advance.

data groups;

input group$ subg$ marks;

cards;

G1 t1 500

G1 t2 300

G1 t3 400

G1 t4 100

G2 t5 600

G2 t6 400

G2 t7 200

G3 t8 650

G3 t9 800

G3 t10 450

run;

i want output as:

GroupSub GMarkstotal
G1t1500800
G1t23001000
G1t3400900
G1t41001200
G2t5600600
G2t6400800
G2t72001000
G3t86501250
G3t98001100
G3t104501450

Accepted Solutions
Solution
‎04-09-2015 02:57 PM
Super User
Posts: 19,869

Re: combinations sum with in the group

Use a SQL summation and then subtract the row value.

data groups;

input group$ subg$ marks;

cards;

G1 t1 500

G1 t2 300

G1 t3 400

G1 t4 100

G2 t5 600

G2 t6 400

G2 t7 200

G3 t8 650

G3 t9 800

G3 t10 450

run;

proc sql;

create table want as

select group, subg, marks, sum(marks)-marks as total

from groups

group by group

order by group, subg;

quit;

proc print data=want;

run;

View solution in original post


All Replies
Solution
‎04-09-2015 02:57 PM
Super User
Posts: 19,869

Re: combinations sum with in the group

Use a SQL summation and then subtract the row value.

data groups;

input group$ subg$ marks;

cards;

G1 t1 500

G1 t2 300

G1 t3 400

G1 t4 100

G2 t5 600

G2 t6 400

G2 t7 200

G3 t8 650

G3 t9 800

G3 t10 450

run;

proc sql;

create table want as

select group, subg, marks, sum(marks)-marks as total

from groups

group by group

order by group, subg;

quit;

proc print data=want;

run;

Frequent Contributor
Posts: 84

Re: combinations sum with in the group

Thank You ReeZa. You always rocks.Smiley Happy

🔒 This topic is solved and locked.

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

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