BookmarkSubscribeRSS Feed
PhatRam33
Fluorite | Level 6

I'm attempting to join two numeric fields (they are both IDs) with two different lengths from two different tables: Field1 has a length of 7 and Field2 has a length of 9, however the first 7 numerical values from field2 equals field1.

 

Example:

 

TableA.Field1 = 1234567

TableB.Field2 = 123456789

 

I attempted the following using PROC SQL and did not have much success.

 

TableA as t1 INNER JOIN TableB as t2 on t1.Field1 = SUBSTR(CAST(t2.Field2 as CHAR(10)),1,7)

 

Any help would be greatly appreciated =).

3 REPLIES 3
Astounding
PROC Star

You don't say how you measure the lack of success, but ...

 

Perhaps the CAST operation is right-hand justifying within the length of 10.  That would cause a mismatch.  If this would work, it eliminates the need for conversion:

 

where t1.field1 = int(t2.field2/100)

 

But it would only work if you always compare 7 digits to 9 digits not (for example) 7 digits to 8 digits.

PhatRam33
Fluorite | Level 6

Lack of Success = 0 lol. I tried a few variations of the join provided and received and error each time primarily related to a data type issue.  Yes, it is always compare 7 to 9 digits. Why would this be in the where clause, just curious?

Astounding
PROC Star

WHERE requires that you compare the same type (character to character, or numeric to numeric).  That's because SAS uses the WHERE clause for filtering.  It can't perform extensive data manipulation, such as type conversions.  So CAST creates a character string and you can't compare that to a numeric.  (I suppose you could CAST bot of them, and compare the first 7 digits of each ... or you could try my original suggestion.)

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