About quintonsmit_gmail_com

quintonsmit_gmail_com · ‎06-11-2015

Thanks to everybody that contributed - I learned something new which I will not forget soon. /* USING FLOATING-POINT (I.E., REAL BINARY) REPRESENTATION THERE HAPPENS TO BE NO EXACT BINARY REPRESENTATION FOR THE DECIMAL VALUES OF 0.1 AND 0.3 3 EXAMPLES BELOW references: http://analytics.ncsu.edu/sesug/2008/PO-082.pdf SAS(R) 9.4 Language Reference: Concepts, Fourth Edition 31560 - Choosing your degree of numeric precision */ ================================================================== ** EXAMPLE USING 0.1 - THIS IS NOT ACCURATE **; data _null_; x=10.1; put "ORIGINAL VALUE:"; put x= 32.30; put x= hex16.; y=10; newx=(x+y)-y; put / "ORIGINAL VALUE AFTER ADDING AND SUBTRACTING 10:"; put newx= 32.30; put newx= hex16.; put / "DIFFERENCE AFTER ADDING AND SUBTRACTING 10:"; diff=x-newx; put diff= 32.30; put diff= hex16.; run; LOG OUTPUT: ORIGINAL VALUE: x=10.10000000000000000000000000000 x=4024333333333333 ORIGINAL VALUE AFTER ADDING AND SUBTRACTING 10: newx=10.10000000000000000000000000000 newx=4024333333333334 DIFFERENCE AFTER ADDING AND SUBTRACTING 10: diff=-.000000000000001776356839400250 diff=BCE0000000000000 ================================================================== ** EXAMPLE USING 0.3 - THIS IS NOT ACCURATE **; data _null_; format ExactVar BinaryVar hex16.; * LOGICALLY THESE ARE THE SAME VALUE *; ExactVar=0.3; BinaryVar=3*0.1; Diff=ExactVar-BinaryVar; * BUT THEY ARE STORED DIFFERENTLY IN HEX/BINARY *; put ExactVar= BinaryVar= Diff= 32.30; run; LOG OUTPUT: ExactVar=3FD3333333333333 BinaryVar=3FD3333333333334 Diff=-.000000000000000055511151231258 ================================================================== ** EXAMPLE USING 0.2 - THIS IS ACCURATE **; data _null_; format ExactVar BinaryVar hex16.; * LOGICALLY THESE ARE THE SAME VALUE *; ExactVar=0.2; BinaryVar=2*0.1; Diff=ExactVar-BinaryVar; * BUT THEY ARE STORED DIFFERENTLY IN HEX/BINARY *; put ExactVar= BinaryVar= Diff= 32.30; run; LOG OUTPUT: ExactVar=3FC999999999999A BinaryVar=3FC999999999999A Diff=0.000000000000000000000000000000 ==================================================================

quintonsmit_gmail_com · ‎06-10-2015

Hi Kurt - thanks for the reply. I am starting to understand this a bit more: == The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math. IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy. For example, the number 1/10 can be represented in a decimal number system with a simple decimal: .1 However, the same number in binary format becomes the repeating binary decimal: .0001100011000111000111 (and so on) This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored. If several arithmetic operations are performed to obtain a given result, these rounding errors may be cumulative.

quintonsmit_gmail_com · ‎06-10-2015

Hi Steven taken from - SAS(R) 9.2 Companion for UNIX Environments ==== Numeric Variable Length and Precision in UNIX Environments The default length of numeric variables in SAS data sets is 8 bytes. The issue of numeric precision affects the return values of almost all SAS math functions and many numeric values returned from SAS procedures. Numeric values in SAS for UNIX are represented as IEEE double-precision floating-point numbers. The decimal precision of a full 8-byte number is effectively 15 decimal digits. ===== So - once again - All of those test numbers should easily fit into a length 8 numeric SAS variable that has precision to 15 decimal digits. Just to clarify - I am using a length 8 numeric variable - the HIGHEST precision that SAS offers. I am not formatting the numeric variable and getting 'error's or mismatches.

quintonsmit_gmail_com · ‎06-10-2015

thanks for the reply - I know about the syntax to round and it is the response that I will suggest to the team. Unfortunately there are 1000's of lines of production code by other developers/analytical teams that will have to be manually checked and corrected. I was looking for an global option that would alleviate the need to manually check those 1000's of lines of production code over hundreds of numeric variables since any change to the production code will have to go through a review process to confirm results and it could be weeks before the manually corrected code is again implemented into production. regards Quinton

quintonsmit_gmail_com · ‎06-10-2015

Thanks Steven - if you look at the test data - a SAS numeric variable of length 8 should be able to store these test values - without accumulating rounding "errors" 299 -299 -283.8 299 283.8 -299 283.8 283.8 -283.8 -283.8 -218.31 218.31 218.31 -218.31 It also doesn't seem to explain why in this test case the GROUP BY function - in this test case only 1 group - produces different totals with the same records. I would expect the same "accumulating rounding errors" to occur since the single group total is the same as the 'grand' total. With testing (changing the order of records) I can produce different 'errors' (value differences) - so I agree it has something to do with the numeric binary problem - I don't see why it should be occurring in this test case though. SAS length 8 numeric variable should be able to store these values - without rounding concerns. Many thanks for all the replies

quintonsmit_gmail_com · ‎06-10-2015

well - for statistics - any inaccuracies is unacceptable 0 (zero) is either 0 (zero) or it is greater than or less than 0 (zero) My challenge is that there are perhaps a hundred different SAS programs dealing with more than 5 SAS data sets each and each SAS data set has many hundreds of variables. So going back to recode (round) each of those variables in all of that code is a huge undertaking - and impracticable right now. I am in the process of migrating from SAS 9.2 to a new SAS 9.4 environment and have to compare the 2 environments - so this might need to be done in both environments to get equal results. Which goes back to the original question(s) - is there a SAS option that can be used to force a SAS data set to be sorted before a proc sql (since this seems to solve the issue but I don't know of such an option) - is there a SAS option that globally will "round" numeric variables - i.e. evaluate using the first 3 decimals only? - why does proc summary produce the expected "correct" result but the proc sql does not? I am just trying to ensure that there is no quick fix solution before going back to the client and informing them that their current code is inaccurate.

quintonsmit_gmail_com · ‎06-10-2015

Unfortunately - SAS DI studio produces a lot of proc sql code - so how reliable are the results? must I go to 20 plus analysts and senior management to explain that SAS proc sql is not 100% accurate - that the scoring models used for profitability are most likely producing inaccurate results. I.E: Depending on the record SORT ORDER - a customer might be scored as profitable? However the same customer with the same records (sales behavior) - but using a different SORT ORDER will NOT be scored profitable?

quintonsmit_gmail_com · ‎06-10-2015

Also - look at the example code. The CORRECT result is returned with a PROC SUMMARY (implies the data gets sorted) where as the PROC SQL .. SUM ... GROUP BY returns a different result to the proc summary on the SAME records when not sorted - but the correct result when the input data set is sorted.

quintonsmit_gmail_com · ‎06-10-2015

Thanks Jaap - I am aware of what you are discussing but that is not the question. This is not about precision or scale. My question: Why does the sort order of the records in the input data set return different total values when summed in the proc sql - when using the SAME individual record & values? Which indicates a SAS proc sql bug.

quintonsmit_gmail_com · ‎06-10-2015

I am hoping somebody can explain why - and how to work around - the following. This comes from sales data - essentially a few sales and then all of the items were returned for a refund - which is why I have positive and the same negative values. The expected net result of all of the transactions is zero. Run the code as is and then remove the comments on the SORT procedure and the proc sql group by will produce 2 different results either: num=.0000000000000568434188608080000 or num=.0000000000000000000000000000000 if num >0 then a sale is counted if num=0 then no sale So depending on the sort order of the input data set I can either have a sale counted or not - this is obviously inaccurate!!! I know I can round and format to get a value closer to zero - that's not the question - at the office there are many examples of this type of coding and not enough time to go and recode all of the analytic code around the enterprise. Preferably somebody has come across this and there is a SAS Option I can use or a hotfix that can be installed. currently tested on: aix 6.1 with SAS 9.2 and aix 7.1 with SAS 9.4 data work.Input_Data; category ='a'; length num 8.; input num; cards; 299 -299 -283.8 299 283.8 -299 283.8 283.8 -283.8 -283.8 -218.31 218.31 218.31 -218.31 ; run; * UNCOMMENT THIS SORT BELOW TO SEE CORRECT RESULTS IN WORK.OUTPUT1 *; /*proc sort data=work.Input_Data; by num; quit;*/ proc sql; * this (group by) sometimes produces incorrect results *; create table WORK.output1 as select category, sum(num) as num from work.Input_Data group by category; * this works *; create table WORK.output2 as select sum(num) as num from work.Input_Data; quit; * this works *; proc summary data=work.Input_Data nway n; class category; var num; output out=WORK.output3 sum=; quit; data _null_; set WORK.output1; * num should be equal to zero - strangely depends on the the order of rows in the work.Input_Data above *; format num tst 32.31; tst=round(num,0.001); put num=; if num >0 then put "Greater"; if num =0 then put "Equal"; if num <0 then put "Less"; put tst=; if tst >0 then put "Greater"; if tst =0 then put "Equal"; if tst <0 then put "Less"; run; data _null_; set WORK.output2; * num equal to zero *; format num 32.31; put num=; if num >0 then put "Greater"; if num =0 then put "Equal"; if num <0 then put "Less"; run; data _null_; set WORK.output3; * num equal to zero *; format num 32.31; put num=; if num >0 then put "Greater"; if num =0 then put "Equal"; if num <0 then put "Less"; run;

quintonsmit_gmail_com · ‎11-14-2014

Hi Gaurav Performing a proc sql left join is not going to create a output dataset with the records in the same order each time. Depending on the how the input datasets are sorted BEFORE the proc sql will effect the order of the records in the output dataset. To test the results across the 2 environments in terms of the records being in the same order, you would want to add a "order by ID" statement to you sql query. ---------------------------------------------------------------------------------------------------------------------------------------------------- TABLE A LEFT JOIN TABLE B ON ID The left join simply states that you would want all the records from TABLE A to be in the output dataset ---------------------------------------------------------------------------------------------------------------------------------------------------- TABLE A LEFT JOIN TABLE B ON ID ORDER BY ID This would output all the records from TABLE A AND the output dataset will be sorted by ID Quinton

Online Status	Offline
Date Last Visited	‎06-24-2020 09:11 AM

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

proc sort - SUM function on group by returns different results dependi...

Re: Same SAS code giving different results on different Linux machine....

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

Re: proc sort - SUM function on group by returns different results dep...

proc sort - SUM function on group by returns different results dependi...

Re: Same SAS code giving different results on different Linux machine....