Hello,
I want to sum two columns by treatment and add a new row called "total" for each treatment into each of the columns that
I want summed. I am using SAS 9.4. Here is an example of what I have and what I am trying to do:
I was able to add the rows pretty easily using the insert function in SQL:
proc sql;
insert into COHORT
values (1,"Total", ., ., .)
values (2,"Total", ., ., .)
values (3,"Total", ., ., .);
quit;
TRT_ARM |
Country |
Efficacy_endpt |
Total_event |
1 |
Peru |
300 |
17 |
1 |
Spain |
48 |
0 |
1 |
Awesomvania |
25 |
8 |
2 |
Peru |
325 |
51 |
2 |
Spain |
46 |
9 |
2 |
Awesomvania |
98 |
5 |
3 |
Peru |
73 |
21 |
3 |
Spain |
45 |
1 |
3 |
Awesomvania |
375 |
30 |
1 |
Total |
. |
. |
2 |
Total |
. |
. |
3 |
Total |
. |
. |
But I am having some difficulty filling the rows. I want them to look like this:
TRT_ARM |
Country |
Efficacy_endpt |
Total_event |
1 |
Peru |
300 |
17 |
1 |
Spain |
48 |
0 |
1 |
Awesomvania |
25 |
8 |
2 |
Peru |
325 |
51 |
2 |
Spain |
46 |
9 |
2 |
Awesomvania |
98 |
5 |
3 |
Peru |
73 |
21 |
3 |
Spain |
45 |
1 |
3 |
Awesomvania |
375 |
30 |
1 |
Total |
373 |
25 |
2 |
Total |
469 |
65 |
3 |
Total |
493 |
52 |
I have tried using the count function in SQL, but that gives me a column for each treatment arm with the sum I want, but it is in its own column.
I also welcome any SUGI papers that you think might help! Thank you!
Donald S.
Use proc means/sql to create the sums, format the table to match your current structure and append the results in using Proc append or SET.
proc sql;
create table totals as
select trt_arm, "Total" as Country, sum(var1) as var1, sum(var2) as var2
from have
group by trt_arm
order by trt_arm;
quit;
data want;
set have totals;
run;
Use proc means/sql to create the sums, format the table to match your current structure and append the results in using Proc append or SET.
proc sql;
create table totals as
select trt_arm, "Total" as Country, sum(var1) as var1, sum(var2) as var2
from have
group by trt_arm
order by trt_arm;
quit;
data want;
set have totals;
run;
Hello Reeza!
Your Solution worked well for me! Thank you! For all those who come back to read this, here is my final code:
proc sort data=have;
by trt_arm;
run;
proc means data = have;
var Efficacy_endpt total_with_event;
by trt_arm;
output sum=Efficacy_endpt total_with_event out=data1;
run;
data sum;
set data1;
drop _TYPE_ _FREQ_;
length ppd $7;
ppd = "Total";
run;
proc sort data = have;
by trt_arm;
run;
proc sort data = sum;
by trt_arm;
run;
data want;
set ER2 sum;
by trt_arm;
if Efficacy_endpt = . then delete; /*When you merge there were two "total" rows, this deletes theuseless deletes the */
run;
I tried this but cant understand.can you please give example?
@akshay007 Please post a new question and link to this one if its similar. Make sure to include the details of what you have and what you need and very clearly illustrate what your problem is.
Please try the datastep
proc sort data=have;
by trt_arm;
run;
data want;
set have;
output;
by trt_arm;
retain sum1 sum2;
if first.trt_arm then do;
sum1=Efficacy_endpt;
sum2=Total_event;
end;
else do;
sum1=sum1+Efficacy_endpt;
sum2=sum2+Total_event;
end;
Efficacy_endpt=sum1;
Total_event=sum2;
country='Total';
if last.trt_arm then output;
drop sum1 sum2;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.