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.:)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.