Help using Base SAS procedures

Remove the leading zero after converting from numeric to character?

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

Remove the leading zero after converting from numeric to character?

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


Accepted Solutions
Solution
‎07-19-2013 11:25 AM
Valued Guide
Posts: 3,208

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

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

---->-- ja karman --<-----

View solution in original post


All Replies
Valued Guide
Posts: 3,208

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

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

---->-- ja karman --<-----
Super Contributor
Posts: 398

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

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

as

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

Valued Guide
Posts: 3,208

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

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.

---->-- ja karman --<-----
Super Contributor
Posts: 398

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

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.

Solution
‎07-19-2013 11:25 AM
Valued Guide
Posts: 3,208

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

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

---->-- ja karman --<-----
Super Contributor
Posts: 398

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

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.

Super User
Posts: 9,662

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

pearl regular expression.

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

Ksharp

Super Contributor
Posts: 297

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

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.

Super User
Posts: 9,662

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

Hi Scott,

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

Ksharp

Super User
Super User
Posts: 6,497

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 2621 views
  • 3 likes
  • 5 in conversation