BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
thb
Fluorite | Level 6 thb
Fluorite | Level 6

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Tom
Super User Tom
Super User

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
Super User

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
PROC Star

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
Fluorite | Level 6 thb
Fluorite | Level 6

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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