05-08-2017 02:18 PM
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.
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 =).
05-08-2017 02:33 PM - edited 05-08-2017 02:34 PM
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.
05-08-2017 02:47 PM
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?
05-08-2017 02:50 PM
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.)