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
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).
Why not convert the character field to numeric and compare those as numerics?
All presentation issues emliniated.
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
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.
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.
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).
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.
pearl regular expression.
data have; input x $; y=prxchange('s/^0//o',-1,x); cards; 0.01 10.01 ; run;
Ksharp
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.
Hi Scott,
Sorry. I have no idea. All my knowledge about Perl Regular Expression is from SAS documentation -- dictionary.
Ksharp
if VAL_ =: '0.' then VAL_ = substr(VAL_,2);
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.