Desktop productivity for business analysts and programmers

JOIN tables

Reply
New User IOF
New User
Posts: 1

JOIN tables

I have this Code

 

PROC SQL;

CREATE TABLE WORK.SNAP_1 AS

SELECT t1.text, 

t1.Date_C01,

t1.Date_C02,

t1.ContryA,

t1.Area,

t1.MONTH,

t1.Revision,

t1.COMGRP,

t2.Group,

t2.Top_Exports,

t2.Top_Imports

 

 FROM WORK.SNAP t1

INNER JOIN WORK.COM_CODES t2 ON (t1.COMGRP = t2.COMGRP);

QUIT;

 

There are 128037 records in T1 (Snap) and because I just want to add additional records to this table I am doing an INNER JOIN

However, the resulting table SNAP_1 has more records

 

Any ideas why this is so? and how can I correct it?

Super User
Posts: 9,860

Re: JOIN tables

If you have several records in t2 that fulfill the join condition for a given record in t1, you will get multiple entries. Make sure before joining that you have only one record in t2 for every comgrp in t1.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 556

Re: JOIN tables

Inner Join brings all the records that are common in both the tables. You may have multiple records per COMGRP in your COM_CODES table. You may need to bring only one record from the table your joining.

 

Check this example: 

data tableA;
Format  Order_date mmddyy10.;
input id item $ Order_date mmddyy10.;
datalines;
1 A 05/24/2018
2 A 05/25/2018
;
run;

data tableB;
Format  del_date mmddyy10.;
input id item $ del_date mmddyy10.;
datalines;
1 A 05/30/2018
1 A 05/26/2018
;
run;

PROC SQl;
create table want as
select a.id,a.item,a.Order_date,b.del_date
from tableA a
	inner join TableB b on (a.id=b.id and a.item=b.item)
	;
quit;
/* Joining tableA with TableB having only one record per group */
PROC SQl;
create table want as
select a.id,a.item,a.Order_date,b.del_date
from tableA a
	inner join (select * 
					from TableB 
						group  by id,item  
						having max(del_date)=del_date) b on (a.id=b.id and a.item=b.item)
	;
quit;

 

Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 2 replies
  • 87 views
  • 0 likes
  • 3 in conversation