DATA Step, Macro, Functions and more

Inner join two dataset matching columns

Accepted Solution Solved
Reply
Occasional Contributor thb
Occasional Contributor
Posts: 7
Accepted Solution

Inner join two dataset matching columns

Hi,

 

I am trying to join two data sets with the same columns, but only if they have the same IDNumbers and Name.  Data1 has Q3 data and Data 2 has Q4 data.  I only want to merge the data if there's at least one month of data for each IDNumber and Name in BOTH Q3 and Q4 datasets.  

 

The variables are the same in both datasets - the only difference is the StartDate.

Variables = IDNumbers, Name, Numerator, Denominator, StartDate

 

I am using Proc Sql, but the merged dataset only includes data from Data1 (Q3).  I'm not sure what I'm doing incorrectly.

 


proc sql;
CREATE TABLE Merge 
AS SELECT a.*, b*
FROM Dataset1 a 
INNER JOIN Dataset2 b
ON a.IDNumbers=b.IDNumbers AND a.Name=b.NAME
ORDER BY StartDate;
quit;

 

 


Accepted Solutions
Solution
‎04-13-2017 09:03 AM
Super User
Posts: 5,082

Re: Inner join two dataset matching columns

A reasonable guess:

 

proc sql;

create table want as

select a.*, b.StartDate as StartDate2

from Dataset1 a, Dataset2 b

where a.IDNumbers = b.IDNumbers and a.Name = b.Name

order by StartDate;

quit;

View solution in original post


All Replies
Super User
Super User
Posts: 6,500

Re: Inner join two dataset matching columns

You probably should post some example data to better explain what you want to happen.

But if you want the results to include records with no matches then you probably need to use FULL JOIN instead of INNER JOIN. 

Super User
Posts: 17,826

Re: Inner join two dataset matching columns

If the variables are the same doesn't your code have errors? What does the log show? You should have to rename the columns since you can't have variables with the same name. 

 

Your criteria is more complex than a single join and you may actually want an append/concatenation. 

As Tom has indicated, you do need to include sample data 

Solution
‎04-13-2017 09:03 AM
Super User
Posts: 5,082

Re: Inner join two dataset matching columns

A reasonable guess:

 

proc sql;

create table want as

select a.*, b.StartDate as StartDate2

from Dataset1 a, Dataset2 b

where a.IDNumbers = b.IDNumbers and a.Name = b.Name

order by StartDate;

quit;

Occasional Contributor thb
Occasional Contributor
Posts: 7

Re: Inner join two dataset matching columns

Thank you so much for your help.  My error was that I did not rename the dataset in the second variables.  

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 147 views
  • 3 likes
  • 4 in conversation