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
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.
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.
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?
...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...
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?
yes, only values that paired were present.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.