Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from set 2

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from set 2

I am trying to Merge two datasets using SAS Enterprise Miner Merge node using a common ID-Student_ID.

I want all records from dataset 1 merged with only the records that has a matching ID entry to data set 1 from the second data set .

Can someone help me to do this?. Right now I get all records from dataset 2 merged to dataset 1


Accepted Solutions
Solution
‎03-09-2017 07:38 AM
SAS Employee
Posts: 63

Re: Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from s

You can use the filter node either before or after the merge. In my example I merged on some predictions values, so the records that did not have a value for phat (my prediction variable) I filtered out. This gave me a dataset with only the rows I wanted.

 

Just be sure to set the properties for Class and interval variables to NONE and click on the ellipse (indicated below) to specify the filter you want.

 

2017-03-08_9-25-12.png

 

Here's what my filter looks like for phat

2017-03-08_9-27-22.png

 

You can also use the SAS code node to filter. Here's a simple example of how that would work.

 

2017-03-08_9-42-18.png

 

 

 

 

View solution in original post


All Replies
SAS Employee
Posts: 63

Re: Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from s

I don't believe you can do this in the merge. You may try filtering either after or before to get your result.

 

I'm not clear if you are looking for an inner join, left or right join based on your post.  If you can clarify I may be able to give a more specific answer.

Occasional Contributor
Posts: 5

Re: Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from s

Thank you for the reply.

 

I'm interested in left join as well as inner join in this case.

If I can do both or either one that would be great.

Right now I'm getting a full join.

Is there any way to atleast filter out the records related to the ID in first set?

Solution
‎03-09-2017 07:38 AM
SAS Employee
Posts: 63

Re: Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from s

You can use the filter node either before or after the merge. In my example I merged on some predictions values, so the records that did not have a value for phat (my prediction variable) I filtered out. This gave me a dataset with only the rows I wanted.

 

Just be sure to set the properties for Class and interval variables to NONE and click on the ellipse (indicated below) to specify the filter you want.

 

2017-03-08_9-25-12.png

 

Here's what my filter looks like for phat

2017-03-08_9-27-22.png

 

You can also use the SAS code node to filter. Here's a simple example of how that would work.

 

2017-03-08_9-42-18.png

 

 

 

 

Occasional Contributor
Posts: 5

Re: Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from s

Thank you  Smiley Happy

This did it! 

I was about to spend a lot of time to do it manually with Python. This saved a lot of my time

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 368 views
  • 2 likes
  • 2 in conversation