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.:)
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: