Join where string from Col1 in Table 1 contained in Col1 from Table 2

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Join where string from Col1 in Table 1 contained in Col1 from Table 2

hi -

 

I want to be able to identify a variable that is titled with additional text in a different table. I thought of performing the sql joins function.

 

Data example:

 

Table A, column ID, variable is the string 'CNM045'

 

In table B, column Name_SP, the related variable contains string 'CNS045 - CNM045 - Emergency Alert.pdf'

 

I want to be able to join these variables (or be able to identify which are related to one another.) Code that I've tried: 

 

proc sql; create table test1 as
select a.*,  b.*
from lib.a as a full outer join  lib.b as b on
a.ID like b.name_sp; quit;

The problem is that my use of the LIKE function is not working, in that 0 variables have joined.

 

 

Any suggestions are appreciated. Thank you


Accepted Solutions
Solution
‎05-26-2016 07:32 AM
Grand Advisor
Posts: 10,223

Re: Join where string from Col1 in Table 1 contained in Col1 from Table 2

Are your Id variable values duplicated in table A?

Do the desired Id value occur in the Name_sp variable on more than one record?

 

If the answer to both of those questions is NO then this may work:

proc sql; create table test1 as
   select a.*,  b.*
   from lib.a as a ,  lib.b as b 
   where  Upcase(b.name_sp) contains upcase(a.ID); 
quit;

If there are multiples you need to decide how those match up. If the duplicates only occur in A and not in B then the above may also do what you want but you need to decide.

 

View solution in original post


All Replies
Solution
‎05-26-2016 07:32 AM
Grand Advisor
Posts: 10,223

Re: Join where string from Col1 in Table 1 contained in Col1 from Table 2

Are your Id variable values duplicated in table A?

Do the desired Id value occur in the Name_sp variable on more than one record?

 

If the answer to both of those questions is NO then this may work:

proc sql; create table test1 as
   select a.*,  b.*
   from lib.a as a ,  lib.b as b 
   where  Upcase(b.name_sp) contains upcase(a.ID); 
quit;

If there are multiples you need to decide how those match up. If the duplicates only occur in A and not in B then the above may also do what you want but you need to decide.

 

Frequent Contributor
Posts: 84

Re: Join where string from Col1 in Table 1 contained in Col1 from Table 2

hi ballardw - thanks for helping me again. I should have been more precise earlier... both my tables contain unique variables, and my output table ideally should retain variables from both tables, hence my though of using an outer join... where  variables id=name_sp match, there will be no blanks in the row, otherwise, there'll be some blanks.  Suggestion?

Frequent Contributor
Posts: 84

Re: Join where string from Col1 in Table 1 contained in Col1 from Table 2

...ok so I managed to get the result I wanted doing a convaluted process... left outer join with table from your code, then full join with my my second orginal table...

Grand Advisor
Posts: 10,223

Re: Join where string from Col1 in Table 1 contained in Col1 from Table 2

So did you have values of A.ID that did not appear in B.name_sp or values of B.name_sp that did not contain any of the A.id, or both?

Frequent Contributor
Posts: 84

Re: Join where string from Col1 in Table 1 contained in Col1 from Table 2

yes, only values that paired were present.

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 336 views
  • 0 likes
  • 2 in conversation