topic Re: Joining tables in SAS Programming

Joining tables

ndee — Mon, 12 Feb 2018 18:21:45 GMT

Hi All,

I have two tables that I need to join based on the date field. Below are the two tables I need to join and the third is the desired table.

I have tried joining them by month of the date field and by Item but the values are adding up. Below table D is what I am getting as an output with the join that I made.

TableA: Quantity

Date Item Quantity Sold

jan2017 kit 22

feb2017 bat 30

march2017 ball 40

jan2018 kit 30

feb2018 bat 22

march2018 ball 50

TableB: Inventory

Date Item Inventory

jan2017 kit 22

feb2017 bat 30

march2017 ball 40

jan2018 kit 40

feb2018 bat 42

march2018 ball 50

Table C(Desired Table):

Date Item Quantity Sold Inventory

jan2017 kit 22 22

feb2017 bat 30 30

march2017 ball 40 40

jan2018 kit 30 40

feb2018 bat 22 42

march2018 ball 50 50

Table D(My Output):

Proc sql;

create table Total as select * from TableA a left join TableB b

on month(a.date)=month(b.date) and

a.Item=b.Item

order by 1;

Date Item Quantity Sold Inventory

jan2017 kit 22 22

jan2017 kit 22 40

feb2017 bat 30 30

feb2017 bat 30 42

march2017 ball 40 40

march2017 ball 40 50

jan2018 kit 30 22

jan2018 kit 30 40

feb2018 bat 22 30

feb2018 bat 22 42

march2018 ball 50 40

march2018 ball 50 50

Any help will be really appreciated. Thank you 🙂

Re: Joining tables

Reeza — Mon, 12 Feb 2018 18:24:35 GMT

Are your dates SAS dates and are you sure you don't have duplicates by date/item in Table A or B?

@ndee wrote:

Hi All,

I have two tables that I need to join based on the date field. Below are the two tables I need to join and the third is the desired table.

I have tried joining them by month of the date field and by Item but the values are adding up. Below table D is what I am getting as an output with the join that I made.

TableA: Quantity

Date Item Quantity Sold

jan2017 kit 22

feb2017 bat 30

march2017 ball 40

jan2018 kit 30

feb2018 bat 22

march2018 ball 50

TableB: Inventory

Date Item Inventory

jan2017 kit 22

feb2017 bat 30

march2017 ball 40

jan2018 kit 40

feb2018 bat 42

march2018 ball 50

Table C(Desired Table):

Date Item Quantity Sold Inventory

jan2017 kit 22 22

feb2017 bat 30 30

march2017 ball 40 40

jan2018 kit 30 40

feb2018 bat 22 42

march2018 ball 50 50

Table D(My Output):

Proc sql;

create table Total as select * from TableA a left join TableB b

on month(a.date)=month(b.date) and

a.Item=b.Item

order by 1;

Date Item Quantity Sold Inventory

jan2017 kit 22 22

jan2017 kit 22 40

feb2017 bat 30 30

feb2017 bat 30 42

march2017 ball 40 40

march2017 ball 40 50

jan2018 kit 30 22

jan2018 kit 30 40

feb2018 bat 22 30

feb2018 bat 22 42

march2018 ball 50 40

march2018 ball 50 50

Any help will be really appreciated. Thank you 🙂

Re: Joining tables

novinosrin — Mon, 12 Feb 2018 18:27:20 GMT

if your sample is an exact representative of your real with both tables having equal number of records, seems like a simple one to one merge will do:

data want;
merge a b;
run;

/*or*/

data want;
set a;
set b;
run;

Re: Joining tables

ndee — Mon, 12 Feb 2018 18:27:49 GMT

Yes, they are sas dates and there are no duplicates.

Re: Joining tables

ndee — Mon, 12 Feb 2018 18:38:52 GMT

Hi, thanks for the reply. No both do not have the same number of records. Table A has another column called Store

Date Item Quantity Sold Store

jan2017 kit 20 new

jan2017 kit 2 old

feb2017 bat 27 new

feb2017 bat 3 old

march2017 ball 40 new

jan2018 kit 30 new

feb2018 bat 22 new

march2018 ball 50 new

I wanna join the inventory from table B by the month cause the total inventory for a month is going to be the same for both stores.

Re: Joining tables

novinosrin — Mon, 12 Feb 2018 18:44:53 GMT

after sorting both by month try

data want;
merge a b;
by month;
run;

/*just play around and test*/

Re: Joining tables

kiranv_ — Mon, 12 Feb 2018 18:50:29 GMT

As @Reeza suggested joining on month will definetly lead to duplicates. just join on date and item and you shoud get the result you want

Proc sql;

create table Total as select * from TableA a left join TableB b

on a.date =b.date and

a.Item=b.Item

order by 1;

Re: Joining tables

ndee — Mon, 12 Feb 2018 18:55:07 GMT

Thank you, that worked. I totally forgot that I can join just by date 😕
Have a nice day all.

Re: Joining tables

Reeza — Mon, 12 Feb 2018 20:28:55 GMT

Or add Year to the condition if the days could vary:

Proc sql;
create table Total as 
select a.date, a.item, a.quantity, b.inventory
from TableA a left join TableB b
on month(a.date)=month(b.date) and
year(a.date) = year(b.date) and
a.Item=b.Item
order by 1;
quit;