topic Re: How to Provide Correct Result on Where Statement By Using Formatted Value in SAS Programming

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

turcay — Mon, 02 May 2016 20:55:02 GMT

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

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

Reeza — Mon, 02 May 2016 21:11:07 GMT

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.

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

PGStats — Mon, 02 May 2016 21:29:44 GMT

... or compare formatted strings

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

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

FreelanceReinh — Mon, 02 May 2016 21:59:01 GMT

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.

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

turcay — Mon, 02 May 2016 22:15:07 GMT

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

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

PGStats — Mon, 02 May 2016 22:33:42 GMT

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

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

FreelanceReinh — Mon, 02 May 2016 22:40:26 GMT

@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.

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

turcay — Mon, 02 May 2016 23:04:41 GMT

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

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

FreelanceReinh — Mon, 02 May 2016 23:56:34 GMT

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.