BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mduarte
Quartz | Level 8

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;

.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

 

 

View solution in original post

9 REPLIES 9
Reeza
Super User

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.)
mduarte
Quartz | Level 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.

 

Reeza
Super User

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
mduarte
Quartz | Level 8

@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?

Reeza
Super User

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;
mduarte
Quartz | Level 8

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

Reeza
Super User

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

LinusH
Tourmaline | Level 20
I'm with @Reeza here. You are definitely trying to round a char variable. Play around, meaning try to get themy in the same format separately, and then do the join.
Data never sleeps
Ksharp
Super User

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5018 views
  • 2 likes
  • 4 in conversation