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

I have a string col that I want to use as a key to join with an integer col in another table.  The join is actually on a substring of the string.  Can I do that in 1 query, or do I have to split it up some and “re-cast” the string?

In the sql join below, a.int_id is an integer which needs to match with the substring in b.string_id.  (Yes, that appears to be my only option for
joining!  These are 2 different systems and they are not modeled well, apparently.)

I'm trying to re-cast that sub-string into an integer and can't figure it out.  I'm connecting to an Oracle v11 database.      

create table myjoin as

select *

from schema1.table1 a

inner join schema2.table2 b on a.int_id = substr(b.string_id,5)

;

quit;

I think I want to re-cast right in the join.  But maybe not.  Maybe it's better to do some sort of nested query where I re-cast the substring in the select and then join to the integer.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

So you are using pass through yes?  I.e.:

proc sql;

     connect to oracle (path=..);

     create table WANT as

     select * from connection to oracle (...);

quit;

You should then be able to do that with the to_number function: Oracle/PLSQL: TO_NUMBER Function

inner join schema2.table2 b on a.int_id = to_number(substr(b.string_id,5))

Of course if it was SAS then you would do:

inner join schema2.table2 b on a.int_id = input(substr(b.string_id,5),best.)

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

So you are using pass through yes?  I.e.:

proc sql;

     connect to oracle (path=..);

     create table WANT as

     select * from connection to oracle (...);

quit;

You should then be able to do that with the to_number function: Oracle/PLSQL: TO_NUMBER Function

inner join schema2.table2 b on a.int_id = to_number(substr(b.string_id,5))

Of course if it was SAS then you would do:

inner join schema2.table2 b on a.int_id = input(substr(b.string_id,5),best.)

ruegsegs_us_ibm_com
Fluorite | Level 6

good question.  I was not using pass-through.  (I'm new to this setup, so I'll investigate more into that.)

schema1 and schema2 are SAS librefs which point to oracle tables. 

DBailey
Lapis Lazuli | Level 10

You should review the requirements that SAS has to have to allow Oracle to do the work,if possible.  For example, if schema1 and schema2 are on the same oracle instance and are accessed with the same userid, then sas will pass the query to oracle to execute as long as there are no sas functions that it can't translate into oracle syntax.  Unfortunately, I believe that substr is one of those functions that while Oracle has an equivalent function, its use as criteria will cause SAS to bring data out of oracle prior to performing the join.

If you use the pass-through, I believe oracle has some implicit conversion rules that would allow the comparison int_id=substr(string_id,1,5).

ruegsegs_us_ibm_com
Fluorite | Level 6

Thanks.  I didn't know I could put the SAS input() function in the join.  That worked perfectly.  Thanks!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 9982 views
  • 0 likes
  • 3 in conversation