topic Re: sum up values from 4 data sets in all numeric columns in SAS Programming

sum up values from 4 data sets in all numeric columns

Ronein — Mon, 25 Mar 2019 15:05:59 GMT

Hello

I want to ask a question please.

Suppose I have 4 data sets with same dimensions (same number of rows and columns) and same columns names.

Suppose that I want to sum up values from 4 data sets (in numeric fields) .

What is the best way to do it?

Data tbl1;
input  z$  x w y;
a 10 20 30
b 20 10 20
c 30 30 40
d 40 15 50
;
run;
Data tbl2;
input  z$  x w y;
a 20 30 60
b 40 30 20
c 40 30 50
d 60 45 50
;
run;
Data tbl3;
input  z$  x w y;
a 10 30 55
b 30 20 20
c 40 30 20
d 20 45 30
;
run;
Data tbl4;
input  z$  x w y;
a 20 10 45
b 20 30 30
c 30 40 30
d 20 25 40
;
run;

Re: sum up values from 4 data sets in all numeric columns

novinosrin — Mon, 25 Mar 2019 15:14:51 GMT

sum by group (key enumeration)?

or sum all (enumerate) all?

or any other?

May I ask what's your desired?

Re: sum up values from 4 data sets in all numeric columns

ballardw — Mon, 25 Mar 2019 15:15:30 GMT

How do you want the output to look? one table of sums after combining the four sets? Four separate sums tables? Are we supposed to sum x with w and y? or sums of the individual variables?

Re: sum up values from 4 data sets in all numeric columns

Ronein — Mon, 25 Mar 2019 15:21:18 GMT

There will be one output table:

a	60	90	190
b	110	90	90
c	130	130	130
d	120	100	170

It means that we sum up values by positions....(like when you do Matrix addition )

Re: sum up values from 4 data sets in all numeric columns

Ronein — Mon, 25 Mar 2019 15:24:26 GMT

The 4 data sets are outputs for 4 separate populations.

Now, the task is to create one table that merge all 4 populations to 1 population

Re: sum up values from 4 data sets in all numeric columns

PaigeMiller — Mon, 25 Mar 2019 15:26:05 GMT

Matrix addition, this is easily done in PROC IML.

If you don't want to use PROC IML, then PROC SUMMARY will work after you combine the data sets into one

data combined;
    set tbl1 tbl2 tbl3 tbl4;
run;
proc summary nway data=combined;
    class z;
    var x w y;
    output out=want sum=;
run;

Re: sum up values from 4 data sets in all numeric columns

novinosrin — Mon, 25 Mar 2019 15:26:16 GMT




Data tbl1;
input  z$  x w y;
cards;
a 10 20 30
b 20 10 20
c 30 30 40
d 40 15 50
;
run;
Data tbl2;
input  z$  x w y;
cards;
a 20 30 60
b 40 30 20
c 40 30 50
d 60 45 50
;
run;
Data tbl3;
input  z$  x w y;
cards;
a 10 30 55
b 30 20 20
c 40 30 20
d 20 45 30
;
run;
Data tbl4;
input  z$  x w y;
cards;
a 20 10 45
b 20 30 30
c 30 40 30
d 20 25 40
;
run;

data all;
set tbl1-tbl4;
run;

proc means data=all nway noprint;
class z;
var x w y;
output out=want(drop=_:) sum=;
run;

z	x	w	y
a	60	90	190
b	110	90	90
c	140	130	140
d	140	130	170

Re: sum up values from 4 data sets in all numeric columns

novinosrin — Mon, 25 Mar 2019 15:30:23 GMT

proc sql;
create table want as
select sum(x) as x, sum(w) as w,sum(y) as y
from
(select * from tbl1
union
select * from tbl2
union
select * from tbl3
union
select * from tbl4)
group by z;
quit;

Re: sum up values from 4 data sets in all numeric columns

Ksharp — Tue, 26 Mar 2019 13:03:14 GMT

Better use UNION ALL , if there is a same obs appeared in two tables, sql would remove one .