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:
Group | Sub G | Marks | total |
G1 | t1 | 500 | 800 |
G1 | t2 | 300 | 1000 |
G1 | t3 | 400 | 900 |
G1 | t4 | 100 | 1200 |
G2 | t5 | 600 | 600 |
G2 | t6 | 400 | 800 |
G2 | t7 | 200 | 1000 |
G3 | t8 | 650 | 1250 |
G3 | t9 | 800 | 1100 |
G3 | t10 | 450 | 1450 |
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;
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;
Thank You ReeZa. You always rocks.:)
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.