DATA Step, Macro, Functions and more

proc sql: equivalent of convert integer to char with trailing zeros

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

proc sql: equivalent of convert integer to char with trailing zeros

[ Edited ]

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;

.

 

 


Accepted Solutions
Solution
‎05-11-2016 01:17 AM
Super User
Posts: 10,044

Re: proc sql: equivalent of convert integer to char with trailing zeros

[ Edited ]

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


All Replies
Super User
Posts: 19,862

Re: proc sql: equivalent of convert integer to char with trailing zeros

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.)
Frequent Contributor
Posts: 84

Re: proc sql: equivalent of convert integer to char with trailing zeros

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.

 

Super User
Posts: 19,862

Re: proc sql: equivalent of convert integer to char with trailing zeros

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
Frequent Contributor
Posts: 84

Re: proc sql: equivalent of convert integer to char with trailing zeros

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

Super User
Posts: 19,862

Re: proc sql: equivalent of convert integer to char with trailing zeros

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;
Frequent Contributor
Posts: 84

Re: proc sql: equivalent of convert integer to char with trailing zeros

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

Super User
Posts: 19,862

Re: proc sql: equivalent of convert integer to char with trailing zeros

@mduarte her Smiley Wink

 

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.  

Super User
Posts: 5,437

Re: proc sql: equivalent of convert integer to char with trailing zeros

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
Solution
‎05-11-2016 01:17 AM
Super User
Posts: 10,044

Re: proc sql: equivalent of convert integer to char with trailing zeros

[ Edited ]

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;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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