BookmarkSubscribeRSS Feed
IOF
Calcite | Level 5 IOF
Calcite | Level 5

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?

2 REPLIES 2
Kurt_Bremser
Super User

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.

SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 650 views
  • 0 likes
  • 3 in conversation