topic Re: Conditionally merge datasets with a substring search in SAS Programming

Conditionally merge datasets with a substring search

q5339 — Thu, 28 Feb 2019 11:28:43 GMT

I want to merge a couple columns from dataset B onto dataset A, but only if A.column contains the substring B.column1. If that condition is met, I want to add B.column2 to dataset A.

I feel like I should be able to do this in proc sql but I'm not sure how to implement the conditional substring search.

Re: Conditionally merge datasets with a substring search

PeterClemmensen — Thu, 28 Feb 2019 11:40:02 GMT

Welcome to the SAS communities 🙂

Sounds like a job that can be done in several ways. Can you be a bit more specific in your requirements?

And can you provide some small example data set and what you want the result to be? Makes it much easier for us to help you 🙂

Re: Conditionally merge datasets with a substring search

q5339 — Thu, 28 Feb 2019 11:50:57 GMT

The relevant column in dataset A contains a bunch of serial numbers that are delimited with "|", so something like "12345|23434|098543".

For dataset B, column 1 contains single serial numbers (e.g. "12345") and column 2 contains dates.

If A.column contains the serial number in B.column1, I want to add B.column2 to A.

Conceptually, it's something like: if find(A.column, B.column1) ~= 0 then A left join B.column2

Re: Conditionally merge datasets with a substring search

RW9 — Thu, 28 Feb 2019 12:55:36 GMT

Post test data in the form of a datastep, see:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Tip 1, never keep multiple data items in one variable, its not a good way to store data and will make your life harder.

Tip 2, Read the documentation, to find one string in another you could use index() SAS function,

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212242.htm

So long as its not pass through SQL that will work fine.

Re: Conditionally merge datasets with a substring search

q5339 — Thu, 28 Feb 2019 14:27:26 GMT

Regarding your first tip, I would normally agree. Unfortunately, the nature of the data I'm using requires me to put multiple items in 1 variable.

For tip 2, if I can't use index or find or contains within SQL, how could I do this conditional join? I've played around using DATA and MERGE but it doesn't seem to produce the desired result.

If you need example data, I suppose this could work

data set1;
input serials:$20.;
datalines;
12345|124|5498
4398|29893|3409|324
3498|0989
;;;

data set2;
input serial:$5. stuff;
datalines;
12345 metadata1
98 metadata2
0989 metadata3
;;;;

The desired output would look like:

serials stuff

12345|124|5498 metadata1

4398|29893|3409|324

3498|0989 metadata3

Re: Conditionally merge datasets with a substring search

PeterClemmensen — Thu, 28 Feb 2019 20:46:57 GMT

What if set2 also contains row 2 like below?

data set2;
input serial:$5. stuff;
datalines;
12345 metadata1
124 metadata2
98 metadata2
0989 metadata3
;;;;