BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brulard
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
ballardw
Super User

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.

 

brulard
Pyrite | Level 9

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?

brulard
Pyrite | Level 9

...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...

ballardw
Super User

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?

brulard
Pyrite | Level 9

yes, only values that paired were present.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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