topic Re: Joining Fields with different lengths using PROC SQL in SAS Programming

Joining Fields with different lengths using PROC SQL

PhatRam33 — Mon, 08 May 2017 18:18:31 GMT

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

Re: Joining Fields with different lengths using PROC SQL

Astounding — Mon, 08 May 2017 18:34:31 GMT

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.

Re: Joining Fields with different lengths using PROC SQL

PhatRam33 — Mon, 08 May 2017 18:47:55 GMT

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?

Re: Joining Fields with different lengths using PROC SQL

Astounding — Mon, 08 May 2017 18:50:28 GMT

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