turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- SAS Proc Report Decimal point highlight issue

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-26-2017 05:53 AM

I use the following Data from a data set where the value is rounded to decimals

Spoiler

Value1 | Value2 | Diff |

62.21 | -62.22 | -0.01 |

65.89 | -65.88 | 0.01 |

-78.3 | 78.39 | -0.09 |

-89.87 | 89.85 | 0.02 |

`Round (Var ,0.01)`

However when i try to display the mismatch record through Proc Report it doesn't highlight / Sometimes it does .

So as a beginner i feel its weird and not sure how sas is reading my data and processing it .

All i'm trying to do is Value1-Value2 = Result

If the result is <-0.01 or >0.01 then Highlight else don’t highlight .

In general im unable to perform subtraction directly . so i had to apply round again and abs to ensure ( - (-) + Becoming - i.e -7(-)+7 = -14 but i expect 0)

```
%ChkDSEmpty(PREMIUM_MISMATCH);
%macro Export;
%if &dsempty. %then %do;
%put WARNING: Export step skipped - no output records.;
%end;
%else %do;
ods html file ='/mnt/agrm-rrr/QIS_DIBIS/Execution/PremiumExtract/BCAR/BCAR_6970_covCarGlass.html' style= sasweb;
PROC REPORT DATA=PREMIUM_MISMATCH NOWD list;
COL CDNUMPOL
AMTPREMIUM AMTPREMIUM_6970CarGlass
AMTEXTFORPAYPERI AMTE4PAPE_6970CarGlass
AMTROUNFORPAYPERI AMTRO4PAPE_6970CarGlass
AMTFINALPREMIUM AMTFINPRM_6970CarGlass
AMTMARGIN AMTMARGIN_6970CarGlass
AMTTAX AMTTAX_6970CarGlass
AMTTOTAL AMTTOTAL_6970CarGlass;
title "bcar 6970 Premium Report";
Define CDNUMPOL / display;
Define AMTPREMIUM / display 'AMTPREMIUM_DIBIS';
Define AMTPREMIUM_6970CarGlass / display 'AMTPREMIUM_QIS';
Define AMTEXTFORPAYPERI / display 'AMTEXTFORPAYPERI_DIBIS';
Define AMTE4PAPE_6970CarGlass / display 'AMTEXTFORPAYPERI_QIS';
Define AMTROUNFORPAYPERI / display 'AMTROUNFORPAYPERI_DIBIS';
Define AMTRO4PAPE_6970CarGlass / display 'AMTROUNFORPAYPERI_QIS';
Define AMTFINALPREMIUM / display 'AMTFINALPREMIUM_DIBIS';
Define AMTFINPRM_6970CarGlass / display 'AMTFINALPREMIUM_QIS';
Define AMTMARGIN / display 'AMTMARGIN_DIBIS';
Define AMTMARGIN_6970CarGlass / display 'AMTMARGIN_QIS';
Define AMTTAX / display 'AMTTAX_DIBIS';
Define AMTTAX_6970CarGlass / display 'AMTTAX_QIS';
Define AMTTOTAL / display 'AMTTOTAL_DIBIS';
Define AMTTOTAL_6970CarGlass / display 'AMTTOTAL_QIS';
COMPUTE AMTPREMIUM_6970CarGlass;
IF ((abs(round(AMTPREMIUM,0.01)) - abs(round(AMTPREMIUM_6970CarGlass,0.01)) < -0.01) OR
(abs(round(AMTPREMIUM,0.01)) - abs(round(AMTPREMIUM_6970CarGlass,0.01)) >0.01)) THEN DO;
call define (_col_,"style","style={background= orange}");
END;
ENDCOMP;
COMPUTE AMTE4PAPE_6970CarGlass;
IF ((abs(round(AMTEXTFORPAYPERI,0.01)) - abs(round (AMTE4PAPE_6970CarGlass,0.01)) < -0.01 ) OR
(abs(round(AMTEXTFORPAYPERI,0.01)) - abs(round (AMTE4PAPE_6970CarGlass,0.01)) > 0.01)) THEN DO;
CALL DEFINE(_col_,'style','style={background= orange}');
END;
ENDCOMP;
COMPUTE AMTRO4PAPE_6970CarGlass;
IF ((abs(round (AMTROUNFORPAYPERI,0.01)) - abs(round (AMTRO4PAPE_6970CarGlass,0.01)) < -0.01) OR
(abs(round (AMTROUNFORPAYPERI,0.01)) - abs(round (AMTRO4PAPE_6970CarGlass,0.01)) > 0.01)) THEN DO;
CALL DEFINE(_col_,'style','style={background= orange}');
END;
ENDCOMP;
COMPUTE AMTFINPRM_6970CarGlass;
IF ((abs(round (AMTFINALPREMIUM,0.01)) - abs(round (AMTFINPRM_6970CarGlass,0.01)) < -0.01) OR
(abs(round (AMTFINALPREMIUM,0.01)) - abs(round (AMTFINPRM_6970CarGlass,0.01)) > 0.01)) THEN DO;
CALL DEFINE(_col_,'style','style={background= orange}');
END;
ENDCOMP;
COMPUTE AMTMARGIN_6970CarGlass;
IF ((abs(round (AMTMARGIN,0.01)) - abs(round (AMTMARGIN_6970CarGlass,0.01)) < -0.01) OR
(abs(round (AMTMARGIN,0.01)) - abs(round (AMTMARGIN_6970CarGlass,0.01)) > 0.01)) THEN DO;
CALL DEFINE(_col_,'style','style={background= orange}');
END;
ENDCOMP;
COMPUTE AMTTAX_6970CarGlass;
IF ((abs(round (AMTTAX,0.01)) - abs(round (AMTTAX_6970CarGlass,0.01)) < -0.01) OR
(abs(round (AMTTAX,0.01)) - abs(round (AMTTAX_6970CarGlass,0.01)) > 0.01)) THEN DO;
CALL DEFINE(_col_,'style','style={background= orange}');
END;
ENDCOMP;
COMPUTE AMTTOTAL_6970CarGlass;
IF ((abs(round (AMTTOTAL,0.01)) - abs(round (AMTTOTAL_6970CarGlass,0.01)) < -0.01) OR
(abs(round (AMTTOTAL,0.01)) - abs(round (AMTTOTAL_6970CarGlass,0.01)) > 0.01)) THEN DO;
CALL DEFINE(_col_,'style','style={background= orange}');
END;
ENDCOMP;
RUN;
ods html close;
%end;
%mend Export;
%Export
```

**Result from the Above code **

**Dataset Datatype**

Can someone help me to resolve this issue

Accepted Solutions

Solution

01-31-2018
05:50 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to srinidelite

12-26-2017 11:52 AM

You are rounding in the wrong place. Let's convert your test data into a dataset and try to report it. First let's show the value of the actual difference between the absolute values and then round that difference.

```
data have ;
input Value1 Value2 ExpectedDiff ;
copy2=value2;
diff1=abs(value1)-abs(value2) ;
diff2=round(diff1,0.01);
format diff: 20.17 ;
cards;
62.21 -62.22 -0.01
65.89 -65.88 0.01
-78.3 78.39 -0.09
-89.87 89.85 0.02
. . .
;
proc print data=have ;
run;
```

Now let's try it with PROC REPORT.

```
proc report data=have ;
define _all_ / display ;
compute value2 ;
if abs(round(abs(value1)-abs(value2),0.01)) > 0.01 then
call define (_col_,"style","style={background= orange}")
;
endcomp;
compute copy2;
if ((abs(round(value1,0.01)) - abs(round(value2,0.01)) < -0.01)
or (abs(round(value1,0.01)) - abs(round(value2,0.01)) >0.01)) then
call define (_col_,"style","style={background= orange}")
;
endcomp;
run;
```

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to srinidelite

12-26-2017 10:45 AM

For the columns in AMTPREMIUM_QIS that appear to be improperly flagged, I believe that you've run into the problem with floating point variables that decimals can't be exactly represented in binary.

```
data Demonstrate;
AMTPREMIUM = 62.34;
AMTPREMIUM_6970CarGlass = 62.33;
if AMTPREMIUM - AMTPREMIUM_6970CarGlass > .01 then
Flag1 = 1;
else Flag1 = 0;
if AMTPREMIUM - AMTPREMIUM_6970CarGlass > .011 then
Flag2 = 1;
else Flag2 = 0;
putlog Flag1= Flag2=;
run;
```

On your AMTEXTFORPAYPERI_QIS issue, because AMTEXTFORPAYPERI is missing, the entire equation will come out to missing, and I believe that missing values are considered less than any number.

Tom

Solution

01-31-2018
05:50 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to srinidelite

12-26-2017 11:52 AM

You are rounding in the wrong place. Let's convert your test data into a dataset and try to report it. First let's show the value of the actual difference between the absolute values and then round that difference.

```
data have ;
input Value1 Value2 ExpectedDiff ;
copy2=value2;
diff1=abs(value1)-abs(value2) ;
diff2=round(diff1,0.01);
format diff: 20.17 ;
cards;
62.21 -62.22 -0.01
65.89 -65.88 0.01
-78.3 78.39 -0.09
-89.87 89.85 0.02
. . .
;
proc print data=have ;
run;
```

Now let's try it with PROC REPORT.

```
proc report data=have ;
define _all_ / display ;
compute value2 ;
if abs(round(abs(value1)-abs(value2),0.01)) > 0.01 then
call define (_col_,"style","style={background= orange}")
;
endcomp;
compute copy2;
if ((abs(round(value1,0.01)) - abs(round(value2,0.01)) < -0.01)
or (abs(round(value1,0.01)) - abs(round(value2,0.01)) >0.01)) then
call define (_col_,"style","style={background= orange}")
;
endcomp;
run;
```