Convert variable types from character to numeric with uncertain length in SAS

Question

When I use PROC SQL statement in SAS, sometimes I need to convert variable from character to numeric or vice versa. I normally use the following two queries:

INPUT(A.KEY_ID, 8.) = B.KEY_ID

OR

A.KEY_ID = PUT(B.KEY_ID, 8.)

My question is, if the length of the variable is either 7 or 8, what length should I put after the KEY_ID? I tried A.KEY_ID = PUT(B.KEY_ID, 7.) and it lost all the records with KEY_ID length=8. And when I used A.KEY_ID = PUT(B.KEY_ID, 8.), it will not be able to find the corresponding KEY_ID with length=7.

Thank you very much!

Added:

Below is the query that I am currently using. LibnameA is a local library and tableA is a local SAS table. DatabaseB is a database that I am connected with. Key_id column is in both tables and used as the key to link them. For example, key_ids are 1234567, 12345678, and ect.

When I used the following query, which I put length 7 in the end of the put statement, all the records with 8 digits key_ids will not able to find a match.

PROC SQL;
CREATE TABLE LIBNAMEA.WORKTABLE AS
SELECT
A.*,
B.VAR1,
B.VAR2
FROM LIBNAMEA.TABLEA A
LEFT JOIN DATABASEb.TABLEB B
ON A.KEY_ID = PUT(B.KEY_ID,8.)
;
QUIT;

Update Results:

If I use the following query, it returns 192758 rows

ON A.KEY_ID = PUT(B.KEY_ID,7.)

If I use the following query, it returns 192923 rows

ON A.KEY_ID = PUT(B.KEY_ID,8.)

If I use the following query, it returns 192757 rows

ON INPUT(A.KEY_ID,8.) = B.KEY_ID

If I use the following query, it returns 192757 rows

ON A.KEY_ID = COMPRESS(PUT(B.KEY_ID,8.))

If I use the following query, it returns 192757 rows

ON COMPRESS(A.KEY_ID) = COMPRESS(PUT(B.KEY_ID,8.))

If I use the following query, it returns 192757 rows

ON INPUT(CATS(A.KEY_ID),8.) = INPUT(CATS(B.KEY_ID),8.)

If I use the following query, it returns 192757 rows

ON A.KEY_ID = PUT(B.KEY_ID,8.-L)

As you can see, only when I use ON A.KEY_ID = PUT(B.KEY_ID,8.), it would return 192923 rows, which is all rows from the table A. However, it will not find the corresponding key_ids in table B, and returns null value in the end results.

Answer 1

If I understand your need, the only problem was comparing the string version of the keys--the numeric one with INPUT worked when I tried it.

So for string keys, this is what worked for me:

a.key_id = trim(left(put(b.key_id,8.)))

You can simplify to:

a.key_id = compress(put(b.key_id,8.))

The issue seems to be where there is a blank in the string key value. You might need to strip that out on both sides.

Answer 2

If you are talking about integers then you are better off converting the values to numbers and comparing them. You can read the 7 character strings and the 8 character strings using the same informat.

 input(a.char_key_id,8.) = b.num_key_id

As to why you are having troubles it probably is because of either leading spaces and/or leading zeros in the character versions. SAS comparisons ignore trailing spaces so you don't need to worry about those.

Leading Zeros

This is the bigger problem. It is more likely to see character variables with leading zeros than leading spaces since most input methods will remove the leading spaces. But the presence of leading zeros can make the same integer value have multiple character representations. So you could represent 123 as '123','0123','00123', etc. This will cause troubles beyond just being able to merge.

Leading Spaces

This will more likely be an issue when you try to convert your integers to strings. The PUT() function normally right aligns the value (hence it generates leading space) whereas most input methods will end up with the value left aligned (trailing spaces). So if you convert the integer 12345 to a string using put(12345,8.) you will result in three leading spaces ' 12345' and it will not match the value you have in your character variable which would have trailing spaces '12345 '. You could add alignment command to the format. And again since SAS ignores the trailing spaces you can just use the longer format.

put(b.num_key_id,F8.-L) = a.char_key_id

Now if you don't know whether your variables are numeric or character and you want code that could work for either than you could use something like this to convert to character and back to numbers. But watch out if your integers are larger than can be represented in 12 digits because SAS will use BEST12. format to convert the numbers.

input(cats(a.key_id),8.) = input(cats(b.key_id),8.)