BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daszlosek
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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;

 

 

daszlosek
Quartz | Level 8

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;
akshay007
Calcite | Level 5

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

Reeza
Super User

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

Jagadishkatam
Amethyst | Level 16

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
daszlosek
Quartz | Level 8
Hello Jagadishkatam,

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

Thank you very much!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 12278 views
  • 3 likes
  • 4 in conversation