SAS Enterprise Guide - Error Joining using strip and put functions with PROC SQL

[GageM]

Hello,

I am trying to create a table using PROC SQL and I want to join on a second table. Without giving the actual column names, the code is essentially this. 

proc sql; 

create table TABLE as 

select distinct

col1,

col2,

...,

case when b.columnX is null then 'Y' else 'N' as columnXNull

from TABLE1 a

left join TABLE2 b on strip(put(a.columnX,30.)) = b.columnX

;quit;

In TABLE1 columnX is Type: Numeric, Length: 8

In TABLE2 columnX is Type: Character, Length: 30

I am getting 2 errors in this code - 

ERROR: Expression using equals (=) has components that are of different data types.

ERROR: Function STRIP requires a character expression as argument 1. 

I originally did not have the put function and was still receiving the above errors, except the second error appeared twice. Since adding the put(), one of the two errors for function strip are gone. However, I am confused, because after reading other forum posts, the put() function should be changing columnX to a character. Am I missing something or doing something wrong? 

[Kurt_Bremser]

Since the result of a PUT function is always chracter, so the ON condition canot cause the second error. Look for another use of the STRIP function in your query.

Run PROC CONTENTS on both datasets to see the types of columnx in both datasets.

[Tom]

It would be easier with SAS code instead of SQL code.  Then SAS should place underlines in the LOG where it is detecting the error.  But for SQL code it does not tell you where your mistake is.  Check for other uses of the STRIP() function.  (also make sure TABLE1 is not a VIEW that possible has a misuse of the STRIP() function in its definition.).

But why are you converting the number in a string instead of converting the string into a number?  If you did want to convert the number to a string there is no need for the STRIP() function on the converted number, you can just use the -L modifier on the format to have it left justify the generated digits.  You might want to add the LEFT() function on the character variable to remove any leading spaces it might have.

on put(a.columnX,30.-L)  = left(b.columnX)

And why did you use a format width of 30?  SAS can only represent about 18 decimal digit precisely.  If you have some 30 digit strings as numbers you will have lost almost half of the digits by having converted them into numbers and so they will probably not match the character string values anyway.

If the values are less than 18 digits then it will be easier to match the values by converting the strings into numbers.  The INPUT() function does not care if the width of the informat is larger than the length of the string being read, so just use 32 as the informat since that is the maximum width it supports.

on a.columnX  = input(b.columnX,32.)