How to Provide Correct Result on Where Statement By Using Formatted Value

[turcay]

Hello,

Nowadays, I’m working on customer place. I don’t know the reason but the default data comes being formatted . Think that there are  18 million rows and I need to use formatted values on Where statement but when I use formatted value on Where statement it brings missing value. I can be two purposes. 1 -> learn how to use formatted value on Where statment to bring right result or 2 -> remove the format of table to use default values on Where statement. Thinking you can help me, I prepared a sample data as below. If you can help me I would be so pleased.

Data Have;
Length ID $ 10 Var1 8;
Infile Datalines Missover;
Input ID Var1;
Format Var1 7.1;
Datalines;
1 12.12456
2 12.26544
3 1.45567
;
Run;
Data Want;
Set Have;
/*Format Var1 7.1;*/
Where Var1=12.1;
Run;

Thank you

[Reeza]

Computers have issue storing numbers, because of binary. Google numerical precision SAS to see why. 

To overcome this round your values before your comparison:

where round(var1, 0.1)  = 12.1;

 EDIT: To clarify - this has nothing to do with formats, it's how computers treats numbers. You'd have the same issue in Excel.  

[PGStats]

... or compare formatted strings

where put(var1, 7.1) = "12.1";

[FreelanceReinh · Accepted solution]

Hello @turcay,

More generally: By default, WHERE and IF conditions work with internal (i.e. unformatted) values. To use formatted values in a condition, you can use the PUT function, as mentioned by @PGStats. (The VVALUE function is an alternative, but not in WHERE conditions.)

In the case of conditions involving numeric variables with floating-point values (like in your example) it is highly recommended to use formatted values (with an appropriate format) or the ROUND function (with an appropriate rounding unit), as @Reeza suggested, because in many cases you don't know the exact (!) internal value.

where put(var1, 7.1)='   12.1';

Please note the three leading blanks. These are required because of the length (7) of format 7.1, unless you apply additional character functions such as LEFT, COMPRESS or STRIP to obtain a left-justified value.

In any case, the selection criterion should be well considered. Depending on the input data, the criteria round(var1, 0.1)=12.1 and round(var1, 0.01)=12.1 could yield very different results.

[turcay]

Hello,

Thank you all of you and for detailed information big thanks to @FreelanceReinh. I was little bit confused. Now, I understand better. Just a short question, do three leading blanks are fixed for formats?

Thank you

[PGStats]

It is simpler to override the default alignment with an alignment specification in the PUT function than to add leading blanks

where put(var1, 7.1 -L) = "12.1";

[FreelanceReinh]

---

@turcay wrote:

Just a short question, do three leading blanks are fixed for formats?

---

No, they are not. In that example exactly three were necessary, because the value '12.1' itself is 4 characters long, the format 7.1 has length 7 (more generally: format w.d has length w), it produces right-justified values and 7 - 4 = 3.

To avoid difficulties with incorrect numbers of leading blanks, you can apply character functions or use the -L format modifier (as PG suggested). For floating-point numbers I mostly use the ROUND function.

[turcay]

Thanks a lot, very useful information. Did you learn these information from any course books or I don't know, somewhere? Thanks

[FreelanceReinh]

I learned the basics of SAS programming primarily from the printed SAS 6 documentation (and the built-in help files of SAS 6.12) in my first job and by doing that job, which was very SAS centric.