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?
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.
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;
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.
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.