topic Re: How to add a new row and sum into a column by treatment in SAS Programming

How to add a new row and sum into a column by treatment

daszlosek — Wed, 10 Feb 2016 22:25:41 GMT

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.

Re: How to add a new row and sum into a column by treatment

Reeza — Wed, 10 Feb 2016 23:00:07 GMT

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;

Re: How to add a new row and sum into a column by treatment

Jagadishkatam — Thu, 11 Feb 2016 00:56:19 GMT

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;

Re: How to add a new row and sum into a column by treatment

daszlosek — Thu, 11 Feb 2016 15:01:14 GMT

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;

Re: How to add a new row and sum into a column by treatment

daszlosek — Thu, 11 Feb 2016 15:02:12 GMT

Hello Jagadishkatam,

I also tried your code and with a little tweaking was able to make it work!

Thank you very much!!

Re: How to add a new row and sum into a column by treatment

akshay007 — Fri, 22 Sep 2017 11:13:40 GMT

I tried this but cant understand.can you please give example?

Re: How to add a new row and sum into a column by treatment

Reeza — Fri, 22 Sep 2017 14:45:12 GMT

@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.