DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

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

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


Accepted Solutions
Solution
‎05-02-2016 07:14 PM
Trusted Advisor
Posts: 1,118

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

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.

View solution in original post


All Replies
Super User
Posts: 19,878

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

[ Edited ]

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.  

Respected Advisor
Posts: 4,937

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

... or compare formatted strings

 

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

PG
Solution
‎05-02-2016 07:14 PM
Trusted Advisor
Posts: 1,118

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

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.

Super Contributor
Posts: 395

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

Posted in reply to FreelanceReinhard

Hello,

 

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

 

Thank you

Respected Advisor
Posts: 4,937

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

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";

PG
Trusted Advisor
Posts: 1,118

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


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.

 

Super Contributor
Posts: 395

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

Posted in reply to FreelanceReinhard
Thanks a lot, very useful information. Did you learn these information from any course books or I don't know, somewhere? Thanks
Trusted Advisor
Posts: 1,118

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

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.

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 310 views
  • 8 likes
  • 4 in conversation