Joining Fields with different lengths using PROC SQL

Reply
Contributor
Posts: 23

Joining Fields with different lengths using PROC SQL

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

Super User
Posts: 5,369

Re: Joining Fields with different lengths using PROC SQL

[ Edited ]

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.

Contributor
Posts: 23

Re: Joining Fields with different lengths using PROC SQL

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?

Super User
Posts: 5,369

Re: Joining Fields with different lengths using PROC SQL

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

Ask a Question
Discussion stats
  • 3 replies
  • 120 views
  • 0 likes
  • 2 in conversation