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;
.
Or do you like Perl Regular Expression ?
NOTE: if data_id in table ONE has decimal. you need change Perl Regular Expression.
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*/
options missing=' ';
proc sql;
select * from one
left join two on
prxmatch(cats('/^',one.data_id,'(\.0*)?$/'),strip(two.data_id));
quit;
You could round it:
round(one.data_id, 1) = round(two.program_id, 1)
Or to convert to Char
input(one.data_id, 8.) = input(two.program_id, 8.)
First - I have just edited question - I had program_id not data_id in the SQL server code. However using your suggestions I get the following error (and a similar error when I use the INPUT function):
ERROR: Function ROUND requires a numeric expression as argument 1.
ERROR: Expression using equals (=) has components that are of different data types.
The way you initially stated the problem, I assumed both were numeric and you were having numeric precision issues.
Given your errors, it appears that's not the case.
It looks like one is character and one is numeric, for the one that is character remove the input function.
For the other add the input function.
Given the error you posted I can't tell which is which, so play around until you get it.
Something like this I would imagine - combine the two above.
input(round(numeric_var, 1), 8.) = char_var
@Reeza - the two data examples (sets named one and two show the format of the data exactly as is in the real life example... ) perhaps you only see the last code chunk in the original question, and not the first one?
I'm apologize, I didn't read your question thoroughly. You did post sample input data and output.
The issue is when you do any conversion the character values get converted to missing and then match with the missing in your data. In my solution I've coded them to -9999 instead. You'll need to decide on a solution that works for your data, but hopefully this helps you get started.
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;
proc sql;
create table want as
select distinct a.*, b.description
from one as a
left join two as b
on round(a.data_id, 1) = case when anyalpha(b.data_id) then -99999
else round(input(b.data_id, 8.), 1) end;
quit;
@Reeza - okay - I had been hoping for a generic solution like that in SQL Server (the convert(varchar(100), convert(numeric(38,20), varname) without the need for a case statement. Your solution in the end is similar to my second sql statement except you have the case statement on the on clause and not in the select and you use distinct. Still an improvement !!!
@LinusH - thanks for your input (genuinely and without sarcasm), however in this instance and in my defense I wasn't actually trying to round a character - only showing Reeza that his first solution wasn't viable....
@mduarte her 😉
There may be another way, probably convert both to character, but you'd still have to convert the number and strip the decimal portion first.
Or do you like Perl Regular Expression ?
NOTE: if data_id in table ONE has decimal. you need change Perl Regular Expression.
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*/
options missing=' ';
proc sql;
select * from one
left join two on
prxmatch(cats('/^',one.data_id,'(\.0*)?$/'),strip(two.data_id));
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: