topic Re: Joining two tables in SAS Programming

Joining two tables

ndee — Thu, 25 Jan 2018 18:37:51 GMT

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.

Re: Joining two tables

Astounding — Thu, 25 Jan 2018 19:59:00 GMT

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.

Re: Joining two tables

ndee — Thu, 25 Jan 2018 19:14:31 GMT

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

Re: Joining two tables

novinosrin — Thu, 25 Jan 2018 19:22:40 GMT

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 ;

Re: Joining two tables

ndee — Thu, 25 Jan 2018 19:42:19 GMT

That worked. Thank you 🙂