[prev in list] [next in list] [prev in thread] [next in thread]
List: sas-l
Subject: Re: Numeric Precision
From: Joe Matise <snoopy369 () GMAIL ! COM>
Date: 2020-09-30 20:29:37
Message-ID: CAM+YpE80FS319u7-=eSG6qbhrmW7b8kB3ZZEAaH-0gX=6MKAXw () mail ! gmail ! com
[Download RAW message or body]
This works for me:
data test;
set sashelp.class;
if mod(_n_,3) eq 0 then pay_amt = 10444555666.25;
else if mod(_n_,3) eq 1 then pay_amt = 10222333444.26;
else pay_amt = 10111222333.26;
run;
data _null_;
set test;
format pay_amt 16.2;
if _n_ = 1 then put "name|age|sex|pay_amt";
put name +(-1) '|' age +(-1) '|' sex +(-1) '|' pay_amt;
run;
Equally, you could add :16.2 instead of 16.2. Otherwise, you're mixing
output types which is what causes the issue.
put name +(-1) '|' age +(-1) '|' sex +(-1) '|' pay_amt :16.2;
Out of curiosity, why don't you use a delimted file, instead of doing so
much extra work to add the colons and the +(-1) stuff?
filename a temp;
data _null_;
set test;
file a dlm='|';
format pay_amt 16.2;
if _n_ = 1 then put "name|age|sex|pay_amt";
put name $ age sex $ pay_amt;
run;
data _null_;
infile a;
input @;
put _infile_;
run;
-Joe
On Wed, Sep 30, 2020 at 2:58 PM Jack Clark <jclark@hilltop.umbc.edu> wrote:
> Joe (and others),
>
>
>
> I'm hoping you can help me with a follow-up question related to this. I
> have formatted my numeric variable with the 16.2 format, and that seems to
> work. I am writing a pipe-delimited text file out from SAS and the numeric
> variable in question is the last variable. Before I added any formatting
> to it, the code looked like this…
>
>
>
> * create txt file ;
>
> data _null_;
>
> set outds.uc3_svcs_&cgrp._&dtstmp.;
>
> file
> "&myfiles_root./dua_&duanum./MCAT/&projdir./Out/uc3_svcs_&cgrp._&dtstmp..txt"
> ;
>
> if _n_ = *1* then put "month|part|setting|place|expenditures";
>
> put month +(-*1*) '|' part +(-*1*) '|' setting +(-*1*) '|' place +(-*1*)
> '|' expenditures;
>
> run;
>
>
>
> It would create a record that looked like the one below. I now know that
> the BEST12. format was being applied. But the EXPENDITURES value always
> started immediately after the last pipe.
>
>
>
> JAN13|Part A|Hospital|Inpatient|10999888777
>
>
>
>
>
> Now I have modified the PUT statement to include the format 16.2.
>
>
>
> put month +(-*1*) '|' part +(-*1*) '|' setting +(-*1*) '|' place +(-*1*)
> '|' expenditures 16.2;
>
>
>
> I am getting the number displayed with the decimals that I was previously
> missing, but the value seems to be right justified. There are several
> leading spaces after the last pipe, like this…
>
>
>
> JAN13|Part A|Hospital|Inpatient| 10999888777.22
>
>
>
> I get it that it may be taking up the full 16 spaces. Is there a way for
> me to tell SAS to left-justify the value so it starts right up against the
> pipe like it used to? Just want to keep the file structure consistent for
> the database team it is being delivered to. Thanks again.
>
>
>
> Jack
>
>
>
> Jack Clark
> Manager of Technical Innovation and Automation
> The Hilltop Institute
> 410-455-6256
> jclark@hilltop.umbc.edu
>
> follow us *[image: https://hilltopinstitute.org/images/twitterteal.png]
> * <https://twitter.com/hilltopinstitut> *[image:
> https://hilltopinstitute.org/images/linkedinteal.png]*
> <https://www.linkedin.com/company/the-hilltop-institute-at-umbc/>
>
>
>
>
>
> *From:* SAS(r) Discussion <SAS-L@LISTSERV.UGA.EDU> *On Behalf Of *Joe
> Matise
> *Sent:* Wednesday, September 30, 2020 3:31 PM
> *To:* SAS-L@LISTSERV.UGA.EDU
> *Subject:* Re: Numeric Precision
>
>
>
>
>
> Jack, you're getting caught here by the BEST12. format. That's the
> default numeric format in SAS in many areas. Notice something about it?
> Yep - it has *12* digits! That happens to be how many digits, including
> the '.', as in 10 billion.
>
>
>
> [12,345,678,901.]23
>
>
>
> So the decimal is cut off! Oops...
>
>
>
> But, note that the value is not lost - it's just not displayed.
>
> proc print data = test2;
>
> format pay best32.;
> run;
>
>
>
> You can apply a better format, perhaps 16.2, since you know what is
> supposed to be happening here.
>
>
>
>
> proc sql;
> select sum(pay_amt) as pay format=16.2
> from test
> ;
> quit;
>
>
>
> If your data can go over 15 digits (plus the decimal for 16), note that
> you should not expect that data to be fully accurate, though you won't lose
> digits - you'll just lose precision, so you'll start by seeing only even
> numbers of cents.
>
>
>
> -Joe
>
>
>
>
>
>
>
>
>
> On Wed, Sep 30, 2020 at 2:17 PM Jack Clark <jclark@hilltop.umbc.edu>
> wrote:
>
> Hello,
>
>
>
> I posted the numeric precision question last week, and am still stumped on
> what is going on with the data I am working with. I am reading hospital
> claims data from a SAS data set with a variable called PAY_AMT. It is the
> payment amount for each claim in dollars and cents. When I look at the
> data it has 2 decimal places.
>
>
>
> I limit the data to the claims I need and then sum up the PAY_AMT variable
> (I am using PROC SUMMARY with the SUM function, but did not have any
> different results using PROC SUMMARY). What I find is that when the sum is
> at least 10 billion (10,000,000,000), I do not get any decimal places in
> the sum. It is always a whole number. This does not seem right to me.
>
>
>
> If feel like I am missing something simple here, but this example shows
> the same behavior. What happens to the decimals?
>
>
>
> *data* test;
>
> pay_amt = *10444555666.25*; output;
>
> pay_amt = *10222333444.26*; output;
>
> pay_amt = *10111222333.26*; output;
>
> ;
>
> *run*;
>
>
>
> *proc* *print* data = test;
>
> *run*;
>
>
>
> *proc* *sql*;
>
> create table test2 as
>
> select sum(pay_amt) as pay
>
> from test
>
> ;
>
> *quit*;
>
>
>
> *proc* *print* data = test2;
>
> r*un*;
>
>
>
>
>
> Jack Clark
> Manager of Technical Innovation and Automation
> The Hilltop Institute
> 410-455-6256
> jclark@hilltop.umbc.edu
>
> follow us *[image: https://hilltopinstitute.org/images/twitterteal.png]
> *
> <https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fhil \
> ltopinstitut&data=02%7C01%7Cjclark%40HILLTOP.UMBC.EDU%7C2986dfb00a404659691208d86577 \
> 5e1d%7Ce9b872148e8f4ad090ec9d5c56c94931%7C0%7C0%7C637370910635204861&sdata=dR0o8TRrdgllIXHszuBrfmTVpXY9aYKv1dBp1ZCVox4%3D&reserved=0>
>
> *[image: https://hilltopinstitute.org/images/linkedinteal.png]*
> <https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com% \
> 2Fcompany%2Fthe-hilltop-institute-at-umbc%2F&data=02%7C01%7Cjclark%40HILLTOP.UMBC.ED \
> U%7C2986dfb00a404659691208d865775e1d%7Ce9b872148e8f4ad090ec9d5c56c94931%7C0%7C0%7C63 \
> 7370910635214862&sdata=nGpkscvK7kbuuEpXt%2BxXbPv0bjnDk68eJtoip6lmp14%3D&reserved=0> \
>
>
>
>
> *From:* SAS(r) Discussion <SAS-L@LISTSERV.UGA.EDU> *On Behalf Of *Joe
> Matise
> *Sent:* Wednesday, September 30, 2020 12:57 PM
> *To:* SAS-L@LISTSERV.UGA.EDU
> *Subject:* Re: Numeric Precision
>
>
>
>
>
> DECIMAL stores them as integers-in-base-ten, so $150.23 is stored as
> 15023. There will never be any imprecision there, unless you pass the
> integer precision limit. If you stored them in SAS as integer numbers of
> cents, you should get the same result.
>
>
>
> -Joe
>
>
>
> On Wed, Sep 30, 2020 at 11:51 AM Tim Berryhill <
> 000008c2afdd9bfa-dmarc-request@listserv.uga.edu> wrote:
>
> I would like to correct one thing I said recently about floating point
> (SAS numbers):
>
> assuming a source provides base ten numbers, SAS converts the base ten
> value to base 16 before adjusting the (hexi-)decimal point, not after.
>
>
>
> This is why in my problem casting the dollars into pennies in the query
> and then summing in PROC MEANS before dividing back into dollars produced a
> consistent result while extracting dollars and summing them produced
> different results depending on the order the records were returned: the
> pennies value is effectively an integer, and safe from rounding effects
> until the value exceeds 15 digits.
>
>
>
> I still don't know how summing in the database is producing consistent
> results—I suspect it is because our DBA's defined the fields expecting us
> to request monthly or even annual totals (made them much bigger than the
> SAS eight byte floating point values). I want to emphasize again, the
> totals did not approach the 16 digit precision limit, so I am not sure how
> the database using a DECIMAL(28,2) definition would make any difference.
> Perhaps I will calculate the total several times in the database, keeping
> it there so I can keep their precision, and compare those totals: maybe the
> database also gets a discrepancy, but out at digit 27 or 26, so it gets
> rounded away when the total is passed to SAS.
>
>
>
> Tim Berryhill
>
>
>
> Credit Portfolio Consultant
>
> Deposit Risk
>
>
>
> Wells Fargo Bank, N.A. l 333 Market Street, 8th Floor l San Francisco,
> CA 94105
>
> MAC A9995-010
>
>
>
> *Timothy.Berryhill@WellsFargo.com <Timothy.Berryhill@WellsFargo.com>*
>
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr">This works for me:<div><br></div><div><br>data test;<br>set \
sashelp.class;<br>if mod(_n_,3) eq 0 then pay_amt = 10444555666.25;<br>else if \
mod(_n_,3) eq 1 then pay_amt = 10222333444.26; <br>else pay_amt = \
10111222333.26;<br>run;<br><br>data _null_;<br>set test;<br>format pay_amt \
16.2;<br>if _n_ = 1 then put "name|age|sex|pay_amt";<br>put name +(-1) \
'|' age +(-1) '|' sex +(-1) '|' \
pay_amt;<br>run;<br></div><div><br></div><div>Equally, you could add :16.2 instead of \
16.2. Otherwise, you're mixing output types which is what causes the \
issue.</div><div><br></div><div> put name +(-1) '|' age +(-1) '|' \
sex +(-1) '|' pay_amt :16.2; <br></div><div><br></div><div>Out of \
curiosity, why don't you use a delimted file, instead of doing so much extra work \
to add the colons and the +(-1) stuff?</div><div><br></div><div><br>filename a \
temp;<br>data _null_;<br>set test;<br>file a dlm='|';<br>format pay_amt \
16.2;<br>if _n_ = 1 then put "name|age|sex|pay_amt";<br>put name $ age sex \
$ pay_amt;<br>run;<br><br>data _null_;<br> infile a;<br> input @;<br> put \
_infile_;<br>run;<br></div><div><br></div><div>-Joe</div><div><br></div><div><br></div><div><br></div><div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Sep 30, 2020 at 2:58 PM \
Jack Clark <<a href="mailto:jclark@hilltop.umbc.edu">jclark@hilltop.umbc.edu</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="EN-US">
<div class="gmail-m_-8690202481640786996WordSection1">
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Joe (and \
others),<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I'm hoping \
you can help me with a follow-up question related to this. I have formatted my \
numeric variable with the 16.2 format, and that seems to work. I am writing a \
pipe-delimited text file out from SAS and the numeric variable in question is the \
last variable. Before I added any formatting to it, the code looked like \
this…<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:10pt;font-family:"Courier \
New";color:green;background:white">* create txt file ;</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"><u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">data _null_;<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> set \
outds.uc3_svcs_&cgrp._&</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">dtstmp.</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">file </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">"&myfiles_root./dua_&duanum./MCAT/&projdir./Out/uc3_svcs_&cgrp._&dtstmp..txt"</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">if _n_ = </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> then put </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">"month|part|setting|place|expenditures"</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">put month +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> part +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> setting +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> place +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> expenditures;<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">run;<u></u><u></u></span></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"><u></u> <u></u></span></p> <p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">It would \
create a record that looked like the one below. I now know that the BEST12. format \
was being applied. But the EXPENDITURES value always started immediately after the \
last pipe.<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal" style="text-indent:0.5in"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">JAN13|Part \
A|Hospital|Inpatient|10999888777<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Now I have \
modified the PUT statement to include the format 16.2.<u></u><u></u></span></p> <p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">put month +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> part +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> setting +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> place +(-</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">1</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:purple;background:white">'|'</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> expenditures 16.2;<u></u><u></u></span></p> \
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I am \
getting the number displayed with the decimals that I was previously missing, but the \
value seems to be right justified. There are several leading spaces after the last \
pipe, like this…<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
JAN13|Part A|Hospital|Inpatient| 10999888777.22<u></u><u></u></span></p> <p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I get it \
that it may be taking up the full 16 spaces. Is there a way for me to tell SAS to \
left-justify the value so it starts right up against the pipe like it used to? \
Just want to keep the file structure consistent for the database team it is being \
delivered to. Thanks again.<u></u><u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Jack<u></u><u></u></span></p>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <div>
<p class="MsoNormal"><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black">Jack Clark<br> \
Manager of Technical Innovation and Automation<br> The Hilltop Institute<br>
410-455-6256</span><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:rgb(31,73,125)"><br> <a \
href="mailto:jclark@hilltop.umbc.edu" target="_blank"><span \
style="color:rgb(0,160,175)">jclark@hilltop.umbc.edu</span></a><u></u><u></u></span></p>
<p class="MsoNormal"><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black">follow us <a \
href="https://twitter.com/hilltopinstitut" target="_blank"><b><span \
style="color:black;text-decoration:none"><img \
alt="https://hilltopinstitute.org/images/twitterteal.png" border="0" height="21" \
id="gmail-m_-8690202481640786996Picture_x0020_7" src="cid:174e0adfd664cff311" \
style="width: 0.2708in; height: 0.2222in;" width="26"> </span></b></a> </span><a \
href="https://www.linkedin.com/company/the-hilltop-institute-at-umbc/" \
target="_blank"><b><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black;text-decoration:none"><img \
alt="https://hilltopinstitute.org/images/linkedinteal.png" border="0" height="26" \
id="gmail-m_-8690202481640786996Picture_x0020_8" src="cid:174e0adfd665b16b22" \
style="width: 0.2708in; height: 0.2708in;" width="26"></span></b></a><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black"><u></u><u></u></span></p>
<p class="MsoNormal"><span style="color:rgb(31,73,125)"><u></u> <u></u></span></p>
</div>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p> <div>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt \
solid rgb(225,225,225);padding:3pt 0in 0in"> <p class="MsoNormal"><b><span \
style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span \
style="font-size:11pt;font-family:Calibri,sans-serif"> SAS(r) Discussion <<a \
href="mailto:SAS-L@LISTSERV.UGA.EDU" target="_blank">SAS-L@LISTSERV.UGA.EDU</a>> \
<b>On Behalf Of </b>Joe Matise<br> <b>Sent:</b> Wednesday, September 30, 2020 3:31 \
PM<br> <b>To:</b> <a href="mailto:SAS-L@LISTSERV.UGA.EDU" \
target="_blank">SAS-L@LISTSERV.UGA.EDU</a><br> <b>Subject:</b> Re: Numeric \
Precision<u></u><u></u></span></p> </div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal" style="background:white"></p></div><p class="MsoNormal"><u></u> \
<u></u></p> </div>
<div>
<p class="MsoNormal">Jack, you're getting caught here by the BEST12. format. \
That's the default numeric format in SAS in many areas. Notice something about \
it? Yep - it has *12* digits! That happens to be how many digits, including the \
'.', as in 10 billion. <u></u><u></u></p> <div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">[12,345,678,901.]23<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">So the decimal is cut off! Oops... <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">But, note that the value is not lost - it's just not \
displayed.<u></u><u></u></p> </div>
<div>
<p class="MsoNormal">proc print data = test2;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">format pay best32.;<br>
run;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">You can apply a better format, perhaps 16.2, since you know what \
is supposed to be happening here.<u></u><u></u></p> </div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><br>
proc sql;<br>
select sum(pay_amt) as pay format=16.2<br>
from test<br>
;<br>
quit;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">If your data can go over 15 digits (plus the decimal for 16), \
note that you should not expect that data to be fully accurate, though you won't \
lose digits - you'll just lose precision, so you'll start by seeing only even \
numbers of cents.<u></u><u></u></p> </div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">-Joe<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">On Wed, Sep 30, 2020 at 2:17 PM Jack Clark <<a \
href="mailto:jclark@hilltop.umbc.edu" target="_blank">jclark@hilltop.umbc.edu</a>> \
wrote:<u></u><u></u></p> </div>
<blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt \
solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"> \
<div> <div>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Hello,</span><u></u><u></u></p>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I posted \
the numeric precision question last week, and am still stumped on what is going on \
with the data I am working with. I am reading hospital claims data from a SAS data \
set with a variable called PAY_AMT. It is the payment amount for each claim in \
dollars and cents. When I look at the data it has 2 decimal \
places.</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I limit \
the data to the claims I need and then sum up the PAY_AMT variable (I am using PROC \
SUMMARY with the SUM function, but did not have any different results using PROC \
SUMMARY). What I find is that when the sum is at least 10 billion (10,000,000,000), \
I do not get any decimal places in the sum. It is always a whole number. This \
does not seem right to me.</span><u></u><u></u></p>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">If feel \
like I am missing something simple here, but this example shows the same behavior. \
What happens to the decimals?</span><u></u><u></u></p>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p> <div>
<p class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">data</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> test;</span><u></u><u></u></p>
<p class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">pay_amt = </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">10444555666.25</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">; </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">output</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">pay_amt = </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">10222333444.26</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">; </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">output</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">pay_amt = </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:teal;background:white">10111222333.26</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">; </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">output</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">run</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><u></u><u></u></p> <p \
class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">proc</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">print</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">data</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> = test;</span><u></u><u></u></p> <p \
class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">run</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><u></u><u></u></p> <p \
class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">proc</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">sql</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">create</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">table</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> test2 </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">as</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">select</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> sum(pay_amt) </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">as</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> pay</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">from</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> test</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> ;</span><u></u><u></u></p> <p \
class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">quit</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><u></u><u></u></p> <p \
class="MsoNormal"><b><span style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">proc</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">print</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><span \
style="font-size:10pt;font-family:"Courier \
New";color:blue;background:white">data</span><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> = test2;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">r</span><b><span \
style="font-size:10pt;font-family:"Courier \
New";color:navy;background:white">un</span></b><span \
style="font-size:10pt;font-family:"Courier \
New";color:black;background:white">;</span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><u></u><u></u></p> <p \
class="MsoNormal"><span style="font-size:10pt;font-family:"Courier \
New";color:black;background:white"> </span><u></u><u></u></p> <p \
class="MsoNormal"><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black">Jack Clark<br> \
Manager of Technical Innovation and Automation<br> The Hilltop Institute<br>
410-455-6256</span><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:rgb(31,73,125)"><br> <a \
href="mailto:jclark@hilltop.umbc.edu" target="_blank"><span \
style="color:rgb(0,160,175)">jclark@hilltop.umbc.edu</span></a></span><u></u><u></u></p>
<p class="MsoNormal"><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black">follow us <a \
href="https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2F \
hilltopinstitut&data=02%7C01%7Cjclark%40HILLTOP.UMBC.EDU%7C2986dfb00a404659691208d \
865775e1d%7Ce9b872148e8f4ad090ec9d5c56c94931%7C0%7C0%7C637370910635204861&sdata=dR0o8TRrdgllIXHszuBrfmTVpXY9aYKv1dBp1ZCVox4%3D&reserved=0" \
target="_blank"><b><span style="color:black;text-decoration:none"><img \
alt="https://hilltopinstitute.org/images/twitterteal.png" border="0" height="21" \
id="gmail-m_-8690202481640786996gmail-m_649582873856620863Picture_x0020_7" \
style="width: 0.2708in; height: 0.2222in;" width="26"> </span></b></a> </span><a \
href="https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.c \
om%2Fcompany%2Fthe-hilltop-institute-at-umbc%2F&data=02%7C01%7Cjclark%40HILLTOP.UM \
BC.EDU%7C2986dfb00a404659691208d865775e1d%7Ce9b872148e8f4ad090ec9d5c56c94931%7C0%7C0%7 \
C637370910635214862&sdata=nGpkscvK7kbuuEpXt%2BxXbPv0bjnDk68eJtoip6lmp14%3D&reserved=0" \
target="_blank"><b><span \
style="font-size:10pt;font-family:Candara,sans-serif;color:black;text-decoration:none"><img \
alt="https://hilltopinstitute.org/images/linkedinteal.png" border="0" height="26" \
id="gmail-m_-8690202481640786996gmail-m_649582873856620863Picture_x0020_8" \
style="width: 0.2708in; height: 0.2708in;" \
width="26"></span></b></a><u></u><u></u></p> <p class="MsoNormal"><span \
style="color:rgb(31,73,125)"> </span><u></u><u></u></p> </div>
<p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p> <div>
<div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt \
solid rgb(225,225,225);padding:3pt 0in 0in"> <p class="MsoNormal"><b><span \
style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span \
style="font-size:11pt;font-family:Calibri,sans-serif"> SAS(r) Discussion <<a \
href="mailto:SAS-L@LISTSERV.UGA.EDU" target="_blank">SAS-L@LISTSERV.UGA.EDU</a>> \
<b>On Behalf Of </b>Joe Matise<br> <b>Sent:</b> Wednesday, September 30, 2020 12:57 \
PM<br> <b>To:</b> <a href="mailto:SAS-L@LISTSERV.UGA.EDU" \
target="_blank">SAS-L@LISTSERV.UGA.EDU</a><br> <b>Subject:</b> Re: Numeric \
Precision</span><u></u><u></u></p> </div>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">DECIMAL stores them as integers-in-base-ten, so $150.23 is \
stored as 15023. There will never be any imprecision there, unless you pass the \
integer precision limit. If you stored them in SAS as integer numbers of cents, \
you should get the same result.<u></u><u></u></p> <div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">-Joe<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"> <u></u><u></u></p>
<div>
<div>
<p class="MsoNormal">On Wed, Sep 30, 2020 at 11:51 AM Tim Berryhill <<a \
href="mailto:000008c2afdd9bfa-dmarc-request@listserv.uga.edu" \
target="_blank">000008c2afdd9bfa-dmarc-request@listserv.uga.edu</a>> \
wrote:<u></u><u></u></p> </div>
<blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt \
solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"> <div>
<div>
<p class="MsoNormal">I would like to correct one thing I said recently about floating \
point (SAS numbers):<u></u><u></u></p> <p class="MsoNormal">assuming a source \
provides base ten numbers, SAS converts the base ten value to base 16 before \
adjusting the (hexi-)decimal point, not after.<u></u><u></u></p> <p \
class="MsoNormal"> <u></u><u></u></p> <p class="MsoNormal">This is why in my problem \
casting the dollars into pennies in the query and then summing in PROC MEANS before \
dividing back into dollars produced a consistent result while extracting dollars and \
summing them produced different results depending on the order the records were \
returned: the pennies value is effectively an integer, and safe from rounding effects \
until the value exceeds 15 digits.<u></u><u></u></p> <p class="MsoNormal"> \
<u></u><u></u></p> <p class="MsoNormal">I still don't know how summing in the \
database is producing consistent results—I suspect it is because our DBA's \
defined the fields expecting us to request monthly or even annual totals (made them \
much bigger than the SAS eight byte floating point values). I want to emphasize \
again, the totals did not approach the 16 digit precision limit, so I am not sure how \
the database using a DECIMAL(28,2) definition would make any difference. Perhaps I \
will calculate the total several times in the database, keeping it there so I can \
keep their precision, and compare those totals: maybe the database also gets a \
discrepancy, but out at digit 27 or 26, so it gets rounded away when the total is \
passed to SAS.<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal" style="text-indent:0.5in">
<span style="font-size:10pt;font-family:Georgia,serif">Tim<b> \
</b>Berryhill</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:8pt;font-family:Verdana,sans-serif"> </span><u></u><u></u></p> <p \
class="MsoNormal" style="text-indent:0.5in"> <span \
style="font-size:8pt;font-family:Verdana,sans-serif;color:black">Credit Portfolio \
Consultant</span><u></u><u></u></p> <p class="MsoNormal" style="text-indent:0.5in">
<span style="font-size:8pt;font-family:Verdana,sans-serif;color:black">Deposit \
Risk</span><u></u><u></u></p> <p class="MsoNormal"><span style="font-size:8pt"> \
</span><u></u><u></u></p> <p class="MsoNormal" style="text-indent:0.5in">
<span style="font-size:8pt;font-family:Verdana,sans-serif;color:gray">Wells Fargo \
Bank, N.A. l 333 Market Street, 8th Floor l San Francisco, CA \
94105</span><u></u><u></u></p> <p class="MsoNormal" style="text-indent:0.5in">
<span style="font-size:8pt;font-family:Verdana,sans-serif;color:gray">MAC \
A9995-010</span><u></u><u></u></p> <p class="MsoNormal"><span \
style="font-size:8pt;color:rgb(90,93,98)"> </span><u></u><u></u></p> <p \
class="MsoNormal" style="text-indent:0.5in"> <u><span \
style="font-size:8pt;font-family:Verdana,sans-serif;color:blue"><a \
href="mailto:Timothy.Berryhill@WellsFargo.com" \
target="_blank">Timothy.Berryhill@WellsFargo.com</a></span></u><u></u><u></u></p> <p \
class="MsoNormal"> <u></u><u></u></p> </div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic