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

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

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

10 REPLIES 10
jakarman
Barite | Level 11

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

---->-- ja karman --<-----
jerry898969
Pyrite | Level 9

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

jakarman
Barite | Level 11

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 --<-----
jerry898969
Pyrite | Level 9

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.

jakarman
Barite | Level 11

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 --<-----
jerry898969
Pyrite | Level 9

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.

Ksharp
Super User

pearl regular expression.

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

Ksharp

Scott_Mitchell
Quartz | Level 8

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.

Ksharp
Super User

Hi Scott,

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

Ksharp

Tom
Super User Tom
Super User

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 4565 views
  • 3 likes
  • 5 in conversation