topic Re: Remove the leading zero after converting from numeric to character? in SAS Procedures

Remove the leading zero after converting from numeric to character?

jerry898969 — Thu, 18 Jul 2013 19:55:34 GMT

Hello,

I have a data set that I need to compare to another dataset. For one of the variables it is numeric on one dataset and character on the other.

So I did

Val_ = trim(left(put(Val, best.))) ;

This works correctly and takes numeric value 0.01 and creates a character variable that is 0.01.

My issue is that the other dataset shows this data as .01. How can I remove just the zero?

I have to make sure not to remove the zero if the data is 10.01.

Thank you for any help

Re: Remove the leading zero after converting from numeric to character?

jakarman — Thu, 18 Jul 2013 20:06:13 GMT

Why not convert the character field to numeric and compare those as numerics?
All presentation issues emliniated.

Re: Remove the leading zero after converting from numeric to character?

Ksharp — Fri, 19 Jul 2013 02:09:58 GMT

pearl regular expression.

data have;
input x $;
y=prxchange('s/^0//o',-1,x);
cards;
 0.01
10.01
;
run;

Ksharp

Re: Remove the leading zero after converting from numeric to character?

Scott_Mitchell — Fri, 19 Jul 2013 08:19:12 GMT

KSHARP Can you please recommend a good PERL Regular Expression internet resource? I would like to learn more about this.

I have researched a few, but thought you might have some insight.

Re: Remove the leading zero after converting from numeric to character?

Tom — Fri, 19 Jul 2013 11:25:47 GMT

if VAL_ =: '0.' then VAL_ = substr(VAL_,2);

Re: Remove the leading zero after converting from numeric to character?

jerry898969 — Fri, 19 Jul 2013 13:36:15 GMT

Thank you everyone for your replies.

I used Jaap's solution and i'm very close to getting it to work. This is what i'm doing now

value_ = input(value, 8.) ;

The only issue i'm having now is that the values look identical but when I do a full join on that variable across the two data sets it get rows back where the values are identical.

proc sql ;

create table chk_data

select a.value_, b.value

from cVal a full join dVal b on a.value_ = b.value

where a.value_ eq . or b.value eq .

;

quit ;

This should return zero rows if all the data matches up but it is returning rows and when I compare the data visually it's identical.

Is sas comparing the numeric values behind the scenes differently?

Thank you

Re: Remove the leading zero after converting from numeric to character?

jakarman — Fri, 19 Jul 2013 14:45:21 GMT

That is an interesting unexpected difference popping up.
The root cause is the nature of floating type involving possible not visible seen precision differences.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference

It does happen easy by the fractions in whole numbers it is more stable. Having done calculations also something can become slightly different.
The most easy solution is comparing rounded values.

The proc SQL usage is setting the direction of having unique values in a full join.

SAS however has a compare procedure. Base SAS(R) 9.4 Procedures Guide Is was thinking this proc would be used (wrong). In this proc the criterion is 0.00001 by default with the method=exact,

For the precision part, it was alway something cumbersome.

Within SAS/acces there are DBTYPE and DBSAStype type options to influence something.
With 9.4 (latest 9.3 updates) the proc DS2 has been added. Base SAS(R) 9.4 Procedures Guide This one knows better the common used DB types.

Sorry for the overload on the background off the precision part.

Re: Remove the leading zero after converting from numeric to character?

jerry898969 — Fri, 19 Jul 2013 14:51:48 GMT

Jaap,

WOW. That is a lot of info.

I'm running on sas 9.2. I have had that issue with the proc compare but not with a full join in a proc sql statement.

What would be the best way to handle this issue? Should I switch back to a character and work with removing the leading zero?

Thanks again for all your help.

Re: Remove the leading zero after converting from numeric to character?

jakarman — Fri, 19 Jul 2013 15:25:57 GMT

The easiest is rounding up ...

I think it will give more cpu overhead, if your dataset is not too big that is the easiest.

Switching back to character is another option.

Is the only difference the leading zero and is this consistent?

Not having the , as seperator or shifting spaces or ..(whatever surprise) ?

Than use the character conversion. Characters are 100% reliable (not having unicode involved).

Re: Remove the leading zero after converting from numeric to character?

jerry898969 — Fri, 19 Jul 2013 15:59:21 GMT

Jaap,

Thank you so much for all of your help.

I'm going to switch it back to character and check that the first two characters of the value equal '0.' and if it is I will substr(value,2) so it will exclude the zero.

Thanks again to you and everyone for the help.

Re: Remove the leading zero after converting from numeric to character?

Ksharp — Mon, 22 Jul 2013 02:01:32 GMT

Hi Scott,

Sorry. I have no idea. All my knowledge about Perl Regular Expression is from SAS documentation -- dictionary.

Ksharp