## Joining two tables

Solved
Occasional Contributor
Posts: 13

# Joining two tables

Hi All,

I have two tables. I need to join them and connect the columns. Below are the two source tables and the third table is the desired table.

Table 1:

date        units        loss

jan17         1            \$10

feb17         2              8

march17     4             11

Table 2:

march17      2          10

apr17           2         10

may17         1          19

Desired table:

jan17       1              10

feb17      2               8

march17  6             21

apr17       2             10

may17       1            19

Any help will be greatly appreciated. Thanks.

Accepted Solutions
Solution
‎01-25-2018 02:41 PM
PROC Star
Posts: 1,547

## Re: Joining two tables

``````data table1;
input date \$        units        loss;
datalines;
jan17         1            10
feb17         2              8
mar17     4             11
;

data table2;
input date \$        units        loss;
datalines;
mar17      2          10
apr17           2         10
may17         1          19
;

proc sort data=table1;
by date;
run;

proc sort data=table2;
by date;
run;

data want;
set table1 table2;
by date;
if first.date then do;sumunits=0;sumloss=0;end;
sumunits+units;sumloss+loss;
if last.date;
run ;``````

All Replies
Super User
Posts: 6,622

## Re: Joining two tables

[ Edited ]

Once you put the tables together, it's just a matter of adding up numbers.  For example:

data table3;

set table1 table2;

run;

proc summary data=table3 nway;

var units loss;

class date;

output out=want (drop=_type_ _freq_) sum=;

run;

proc print data=want;

run;

*** EDITED:  Sorry, I should have checked this more carefully.  Notice the added statement.

Occasional Contributor
Posts: 13

## Re: Joining two tables

Thanks for replaying.

By doing this: data table3;
set table1 table2;
run;

table 3:
jan17        1      10
feb17        2       8
march17   4        11
march17    2         10
apr17         2      10
may17        1       19

And the proc summary step is summing up all the values and giving out just one observation:

N        loss

12       68

The output I wanted was a table with march being added up like this:

jan17        1      10
feb17        2       8
march17   6        21
apr17         2      10
may17        1       19

Solution
‎01-25-2018 02:41 PM
PROC Star
Posts: 1,547

## Re: Joining two tables

``````data table1;
input date \$        units        loss;
datalines;
jan17         1            10
feb17         2              8
mar17     4             11
;

data table2;
input date \$        units        loss;
datalines;
mar17      2          10
apr17           2         10
may17         1          19
;

proc sort data=table1;
by date;
run;

proc sort data=table2;
by date;
run;

data want;
set table1 table2;
by date;
if first.date then do;sumunits=0;sumloss=0;end;
sumunits+units;sumloss+loss;
if last.date;
run ;``````
Occasional Contributor
Posts: 13