Inner join two dataset matching columns

[thb]

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;

[Tom]

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. 

[Reeza]

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 

[Astounding · Accepted solution]

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;

[thb]

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