DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-11-2016 09:59 AM
Super User
Posts: 17,784

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

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;

 

 

View solution in original post


All Replies
Solution
‎02-11-2016 09:59 AM
Super User
Posts: 17,784

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

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;

 

 

Contributor
Posts: 69

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

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;
Trusted Advisor
Posts: 1,128

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

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;
Thanks,
Jag
Contributor
Posts: 69

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

Hello Jagadishkatam,

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

Thank you very much!!
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 951 views
  • 3 likes
  • 3 in conversation