topic Re: Inner join two dataset matching columns in SAS Programming

Inner join two dataset matching columns

thb — Thu, 13 Apr 2017 04:09:42 GMT

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;

Re: Inner join two dataset matching columns

Tom — Thu, 13 Apr 2017 04:13:14 GMT

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.

Re: Inner join two dataset matching columns

Reeza — Thu, 13 Apr 2017 05:04:58 GMT

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

Re: Inner join two dataset matching columns

Astounding — Thu, 13 Apr 2017 06:33:22 GMT

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;

Re: Inner join two dataset matching columns

thb — Thu, 13 Apr 2017 13:02:53 GMT

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