DATA Step, Macro, Functions and more

formatting for less than 1 percent

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 141
Accepted Solution

formatting for less than 1 percent

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!

 

 


Accepted Solutions
Solution
‎01-11-2016 12:01 PM
Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

[ Edited ]

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.

 

 

View solution in original post


All Replies
Valued Guide
Posts: 860

Re: formatting for less than 1 percent

Posted in reply to elwayfan446
Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

Posted in reply to Steelers_In_DC

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;

Super User
Posts: 11,336

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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

Trusted Advisor
Posts: 1,117

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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.

SAS Super FREQ
Posts: 3,752

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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

Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

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

 

 

Super User
Super User
Posts: 7,942

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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.

Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

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


Capture.JPG
Super User
Posts: 11,336

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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.

Super User
Super User
Posts: 7,942

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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?

Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

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

 

Capture.JPG

 

 

SAS Super FREQ
Posts: 3,752

Re: formatting for less than 1 percent

Posted in reply to elwayfan446

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.

 

Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

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

 

Capture.JPG

Solution
‎01-11-2016 12:01 PM
Frequent Contributor
Posts: 141

Re: formatting for less than 1 percent

[ Edited ]

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.

 

 

☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 365 views
  • 2 likes
  • 6 in conversation