Joining two tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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
a month ago
PROC Star
Posts: 829

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 ;

View solution in original post


All Replies
Super User
Posts: 5,995

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

Posted in reply to Astounding

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
a month ago
PROC Star
Posts: 829

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

Re: Joining two tables

Posted in reply to novinosrin
That worked. Thank you Smiley Happy
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 124 views
  • 0 likes
  • 3 in conversation