formatting for less than 1 percent

[elwayfan446]

I am having trouble keeping values of less than 1 percent from rounding to 1.  Can you tell me what I am missing?

I need this number to 0.0023610361575823 only round to 0.0024 at the most.

Thank you!

[Steelers_In_DC]

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p0tj6cmga7p...

[elwayfan446]

Thanks.  I found that article and tried a few of them and they are still rounding to 1.  I tried this specifically thinking it would be the one.

d7 = round(1234.56789,.0001) - 1234.5679;

[ballardw]

What FORMAT is used display the variable? Run Proc Contents or use the table column view to examine variable properties. If the format tells SAS to display fewer decimal points then you want that's all that appear. You may to assign a format like F10.6

[FreelanceReinh]

Do you store the result of this SQL expression in a variable? (And why do you refer to "percent"?)

So, let's say you have a numeric variable which contains the value 0.0023610361575823. Now you could either round this value to four decimals or simply apply a format to achieve the same displayed result of 0.0024:

data _null_;
x=0.0023610361575823;
rx=round(x,.0001);
put rx;
put x 6.4;
run;

The format approach (see the second PUT statement above, using format 6.4) has the advantage that you don't have to create a new variable to hold the rounded value (rx in the example above), let alone to overwrite your existing value with a less precise one.

In your second example, you define variable d7 to have the value 0, because the ROUND function returns exactly the value you subtract from it.

[Rick_SAS]

The ROUND function has a second argument that specifies the unit of rounding.  To round to the nearest 0.001, call it as

r = round( 0.0023610361575823, 0.0001); 

Look at the example near the end of the article "Rounding Up, Rounding Down."

[elwayfan446]

Yeah, I am trying that too.  Still rounding up to 1.  Here is the exression I am using:

ROUND((COUNT(t1.DEL30))/(COUNT(t1.LOAN_NUM)),0.0001)

The numbers in this expression are:

ROUND((35/14824),0.0001)

35/14824 = 0.0023610361575823

[RW9]

So, if you run the code:

data tmp;
  a=ROUND((35/14824),0.0001);
run;
  

Your saying that you don't get a being 0.0024?  I would suggest then its a SAS helpdesk issue as it works fine on my machine.  However it looks to me like your using SQL there.  Please post, some test data as a datastep, and the exact full code you are running so that I can replicate the issue on my machine.

[elwayfan446]

I am creating an advanced expression field in Enterprise Guide 6.1 with the following expression:

ROUND((COUNT(t1.DEL30))/(COUNT(t1.LOAN_NUM)),0.0001)

The values of thouse count functions are 35/14824 and the results I get are

[ballardw]

Again, what FORMAT is the variable using to display values?

If I have a value of 0.00024 and display it with a Percent6.1 the Displayed value is 0.0%, with Percent10.4 0.0240%, best1. shows 0, best4. shows 0, F4.1 shows 0.0.

I have custome formats that cold display a value that small as "<0.05". The format could well be assigning ANY value between 0 and 1 as 1 and have very little with how you are getting the actual numeric value.

[RW9]

Ah, so your using EG, its generally a good idea to post in the relevant sub forum.  I don't use EG, but I would guess, for what you have posted and what you show there, that it is the format on the PERCENTDELQLOANCOUNT is hiding the actual value.  As I said, I don't use EG, but in Base SAS, after an SQL expression like that you can specify the format:

ROUND((COUNT(t1.DEL30))/(COUNT(t1.LOAN_NUM)),0.0001) as PERCENTDELQLOANCOUNT format=8.4

Don't know how you would do the same in this "advanced expression field" maybe there is an option for it on column properties?

[elwayfan446]

Correct, you specify that on the column properties.

Sorry for not posting this in the EG forum.  I was thinking it was a simple SAS format issue.

ballard, I have tried different put formats.  Currently the format I am using is 6.4 and it is showing as 1.0000

[Rick_SAS]

Are you sure that the subexpressions are 35 and 14824? The COUNT function returns the number of nonmissing values in a column.  Perhaps you are using zeros for the del30 variable?

Here is data that shows the issue:

data a;
do i = 1 to 35;
   del30 = 1; Loan_NUM=i; output;
end;
do i = 36 to 14824;
   del30 = .; Loan_NUM=i; output;
end;
run;

If you define a new variable by using use

round( count(del30) / count(Loan_NUM), 0.0001)

you will get 0.0024, as expected. However, if you have 0 instead of missing for the del30 variable, then the expression will evaluate to 1.

[elwayfan446]

Rick, yes, there are 0 values in the data, not missing values.  However, in my sql expression to get the counts, I use a where clause to exclude observations that are 0.  where DEL30 <> 0

The results....

[elwayfan446 · Accepted solution]

Rick,

After thinking about your reply a little bit more... I figured it out.  In Enterprise Guide, I was using the advanced expression variable that I had created to get the counts originally instead of using the expression that I used to get it.  So, it was not using the where DEL30 <> 0 criteria that the original variable was using.  I had to literally write it again for the new variable and it worked.  So...

Instead of (which were the original calculated variables)

count(del30) / count(Loan_NUM) 

I had to use the criteria to get the count in the new variable:

(SELECT
       COUNT(LOAN_NUM) 
FROM MSRADHOC.MSR_2015_ONBOOK_DELQ
WHERE DEL30 <> 0)/(COUNT(t1.LOAN_NUM))

Thank you for triggering the thought process on this.  I appreciate everyone's help this morning.