topic Re: JOIN tables in SAS Enterprise Guide

JOIN tables

IOF — Thu, 24 May 2018 11:12:27 GMT

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?

Re: JOIN tables

Kurt_Bremser — Thu, 24 May 2018 11:33:18 GMT

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.

Re: JOIN tables

SuryaKiran — Thu, 24 May 2018 12:41:16 GMT

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;