Hi, I have an id stored as '14344.000000000000000000' in one table and as 14344 in another. How can I use proc sql to match them correctly for the following circumstances data one; input data_id 8.; datalines; 14344 0 . . ; run; data two; input data_id $1-27 description $ 28-47; datalines; 14344.00000000000000000000 my_data_description not_like_a_number1 another_description not_like_a_number2 description_2 ; run; /*incorrect*/ proc sql; select * from one left join two on one.data_id = input(two.data_id, 8.); run; /*incorrect, convert char to numeric and use case statement: Have multiple lines for data_one.data_id = .*/ select one.*, case when one.data_id NE . then two.description else '' end as description from one left join two on one.data_id = input(two.data_id, 8.); run; quit; Desired output data_id description 14344 my_data_description 0 . Note that the following works in SQL server and is what I want to replicate using proc sql select *
from one
left join two on convert(varchar(100),convert(numeric(38,20),one.data_id)) = two.data_id; .
... View more