DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
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: 19,815

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

Posted in reply to daszlosek

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: 19,815

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

Posted in reply to daszlosek

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;

 

 

Frequent Contributor
Posts: 76

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;
New User
Posts: 1

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

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

Super User
Posts: 19,815

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

Posted in reply to akshay007

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

Trusted Advisor
Posts: 1,137

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

Posted in reply to daszlosek

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
Frequent Contributor
Posts: 76

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

Posted in reply to Jagadishkatam
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
  • 6 replies
  • 1652 views
  • 3 likes
  • 4 in conversation