topic Re: Merging two data sets using SASEnterprise Miner Merge node -keep only matching Id records from s in SAS Data Science

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

Piyu — Mon, 06 Mar 2017 05:07:08 GMT

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

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

MelodieRush — Tue, 07 Mar 2017 18:46:45 GMT

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.

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

Piyu — Wed, 08 Mar 2017 05:31:30 GMT

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?

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

MelodieRush — Wed, 08 Mar 2017 14:43:10 GMT

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.

Here's what my filter looks like for phat

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

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

Piyu — Thu, 09 Mar 2017 12:37:39 GMT

Thank you 🙂

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