topic Re: data manipulation in New SAS User

data manipulation

Romain69100 — Thu, 18 Aug 2022 16:55:16 GMT

Dear SAS users,

I am currently working on a project where i built a numeric score called "SCORE", this variable range from 0 to 0.90 with the following modalities:

-0.2

-0.31

-0.40

-0.60

-0.75

-0.80

-0.90

When i performed a proc freq on this variable i got effective by modalities. However when i tried this code:

data toto;

set toto;

if score=0.2 then output;

run;

0 observation is printed. I have the same problem with the 0.31 value. It worked well for the other values....

I have checked number of decimals and format and it is well set up.

I do not know what is happening and why i get an output with the proc freq but nothing with the data step...

Any help would be grandly appreciated.

Thanks

Re: data manipulation

PaigeMiller — Thu, 18 Aug 2022 17:06:08 GMT

@Romain69100 wrote:

Dear SAS users,

I am currently working on a project where i built a numeric score called "SCORE", this variable range from 0 to 0.90 with the following modalities:

-0.2

-0.31

-0.40

-0.60

-0.75

-0.80

-0.90

When i performed a proc freq on this variable i got effective by modalities. However when i tried this code:

data toto;

set toto;

if score=0.2 then output;

run;

0 observation is printed. I have the same problem with the 0.31 value. It worked well for the other values....

I have checked number of decimals and format and it is well set up.

I do not know what is happening and why i get an output with the proc freq but nothing with the data step...

Any help would be grandly appreciated.

Are there errors or warnings in the log? If so, show us the ENTIRE log for this DATA step (we need to see every single line, every single character — do not pick and choose parts of the log to show us and not show us the rest of the log). Please click on the </> icon and paste the log as text into the window that appears.

The first possibility that comes to mind is that SCORE is actually a character variable and it does not have value 0.2 but its value is character string '0.2'. You should get errors in the log if this is the case.

The second possibility is that SCORE is numeric but its value is for some reason is not 0.2 in your data set but some value that is extremely close to 0.2 but not exactly equal because of round-off error or machine precision problems.

Re: data manipulation

Reeza — Thu, 18 Aug 2022 17:11:39 GMT

Suspect this is a numeric precision issue, does the following work:

data toto2;

set toto;

if round(score, 0.1)=0.2 then output;

run;

Re: data manipulation

Romain69100 — Thu, 18 Aug 2022 17:15:49 GMT

Thank you for your answer. No warnings/errors in the log. Score is numeric and i have only 2 decimals... It worked well for other values but for some reasons not for 0.20 and 0.31....

Re: data manipulation

Romain69100 — Thu, 18 Aug 2022 17:17:22 GMT

Thank you for your answer. I have only 2 decimals for all score values. It worked well for other values but for some reasons not for 0.20 and 0.31....

Re: data manipulation

PaigeMiller — Thu, 18 Aug 2022 17:21:10 GMT

@Romain69100 wrote:

Thank you for your answer. I have only 2 decimals for all score values. It worked well for other values but for some reasons not for 0.20 and 0.31....

You mean IT LOOKS LIKE you have only two digits ... the real value could be something different. The real value could really be (even if you don't see it) 0.1999999999999 (and so SAS shows you 0.2) and this is not equal to 0.2. So you could try something like this to test if this is what is happening.

if fuzz(score - 0.2) = 0 then output;

Re: data manipulation

Reeza — Thu, 18 Aug 2022 17:19:52 GMT

@Romain69100 wrote:

Thank you for your answer. I have only 2 decimals for all score values. It worked well for other values but for some reasons not for 0.20 and 0.31....

Numerical Accuracy in SAS:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrcon/p0ji1unv6thm0dn1gp4t01a1u0g6.htm

Did the code work?

Re: data manipulation

Kurt_Bremser — Thu, 18 Aug 2022 17:19:55 GMT

@Romain69100 wrote:

Thank you for your answer. I have only 2 decimals for all score values. It worked well for other values but for some reasons not for 0.20 and 0.31....

Did you actually try the ROUND function? If not, DO IT.

Re: data manipulation

Romain69100 — Thu, 18 Aug 2022 17:25:23 GMT

Thanks i tried your code and it does not fix the problem. 0.20 and 0.31 are the REAL values

Re: data manipulation

Romain69100 — Thu, 18 Aug 2022 17:27:02 GMT

Yes i tried it. The problem is still present. It is not a decimal/round problem because 0.20 and 0.31 are the real and entire values..

Re: data manipulation

Reeza — Thu, 18 Aug 2022 17:29:40 GMT

Please read the previous article I linked, and post the log from the code ~~I posted~~. below.

Forgot the negative sign.

data toto2;

set toto;

if round(score, 0.1)=-0.2 then output;

run;

Re: data manipulation

Reeza — Thu, 18 Aug 2022 17:52:32 GMT

Is it -0.2 and -0.31 or 0.2 and 0.31?

@Romain69100 wrote:

Thanks i tried your code and it does not fix the problem. 0.20 and 0.31 are the REAL values

Re: data manipulation

PaigeMiller — Thu, 18 Aug 2022 18:00:20 GMT

@Romain69100

I think this has gone on long enough with us guessing what is happening. We need to see (a portion of) your actual data set as SAS data step code (please follow these instructions); and we need to see the code you are using.

Re: data manipulation

PaigeMiller — Thu, 18 Aug 2022 17:45:20 GMT

@Reeza wrote:

Please read the previous article I linked, and post the log from the code ~~I posted~~. below.

Forgot the negative sign.
data toto2;

set toto;

if round(score, 0.1)=-0.2 then output;

run;

@Reeza , this not only would output values of -0.2 but it would also output when the value is -0.22, which doesn't seem like a good general purpose strategy here.

Re: data manipulation

Reeza — Thu, 18 Aug 2022 17:52:58 GMT

OP has stated those values are not possibly in the data set though and easily adjusted for two decimal places if this is the issue.

@PaigeMiller wrote:
@Reeza wrote:

Please read the previous article I linked, and post the log from the code ~~I posted~~. below.

Forgot the negative sign.
data toto2;

set toto;

if round(score, 0.1)=-0.2 then output;

run;
@Reeza , this not only would output values of -0.2 but it would also output when the value is -0.22, which doesn't seem like a good general purpose strategy here.

Re: data manipulation

Kurt_Bremser — Fri, 19 Aug 2022 08:17:57 GMT

Just to put this thing to rest:

data have;
input score;
format score 5.2;
datalines;
0.2
0.31
0.4
0.6
0.75
0.8
0.9
;

proc freq data=have;
tables score;
run;

data want;
set have;
if score = 0.2;
run;

Final part of the log:

 86         data want;
 87         set have;
 88         if score = 0.2;
 89         run;
 
 NOTE: There were 7 observations read from the data set WORK.HAVE.
 NOTE: The data set WORK.WANT has 1 observations and 1 variables.

So, if the value is 0.2, then the subsetting IF will work, period. If it doesn't work, then you have a different value, but don't see it because of the format.

How are the values created?

And please do post the log from this:

data toto;
set toto;
if score=0.2 then output;
run;

Re: data manipulation

FreelanceReinh — Fri, 19 Aug 2022 15:33:51 GMT

Fun fact: Using Windows SAS 9.4M5 with the default setting of system option DECIMALCONV, i.e.,

options decimalconv=COMPATIBLE;

there are seventeen distinct numeric values which are not equal to 0.2, yet indistinguishable from 0.2 when viewed in the common numeric formats w.d, BESTw. and Ew. (with all possible values of w and d). Similarly, 0.31 has eight such "doubles" among the internal numeric representations.

The log of the code below shows where the corresponding ranges start and end.

data _null_;
input score hex16.;
put score hex16. +2 (5*score) (best16.-l best32.-l +1 16.15 +1 32.30 e32.);
cards;
3FC9999999999997
3FC9999999999998
3FC99999999999A9
3FC99999999999AA
3FD3D70A3D70A3D6
3FD3D70A3D70A3D7
3FD3D70A3D70A3DF
3FD3D70A3D70A3E0
;