BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
elwayfan446
Barite | Level 11

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

15 REPLIES 15
elwayfan446
Barite | Level 11

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
Super User

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
Jade | Level 19

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
SAS Super FREQ

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
Barite | Level 11

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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
ballardw
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Barite | Level 11

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

 

 

Rick_SAS
SAS Super FREQ

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
Barite | Level 11

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

elwayfan446
Barite | Level 11

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.

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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