topic Re: proc sql: equivalent of convert integer to char with trailing zeros in SAS Programming

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

mduarte — Tue, 10 May 2016 03:13:21 GMT

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

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;

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

Reeza — Tue, 10 May 2016 03:09:58 GMT

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

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

mduarte — Tue, 10 May 2016 03:15:04 GMT

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.

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

Reeza — Tue, 10 May 2016 03:23:20 GMT

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

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

mduarte — Tue, 10 May 2016 04:46:13 GMT

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

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

Reeza — Tue, 10 May 2016 05:03:33 GMT

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;

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

LinusH — Tue, 10 May 2016 05:06:35 GMT

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.

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

mduarte — Tue, 10 May 2016 05:17:26 GMT

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

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

Reeza — Tue, 10 May 2016 05:24:16 GMT

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

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

Ksharp — Tue, 10 May 2016 06:09:39 GMT

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;