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;
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;
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.
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
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;
Thank you so much for your help. My error was that I did not rename the dataset in the second variables.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.