Desktop productivity for business analysts and programmers

SAS Proc Report Decimal point highlight issue

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

SAS Proc Report Decimal point highlight issue

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

 

 

Spoiler
Value1Value2Diff
62.21-62.22-0.01
65.89-65.880.01
-78.378.39-0.09
-89.8789.850.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 

 Capture.PNG

Dataset Datatype

Capture_datatype.PNG

Can someone help me to resolve this issue 


Accepted Solutions
Solution
‎01-31-2018 05:50 AM
Super User
Super User
Posts: 7,860

Re: SAS Proc Report Decimal point highlight issue

Posted in reply to srinidelite

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;

image.png

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;

image.png

 

View solution in original post


All Replies
PROC Star
Posts: 1,265

Re: SAS Proc Report Decimal point highlight issue

Posted in reply to srinidelite

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
Super User
Super User
Posts: 7,860

Re: SAS Proc Report Decimal point highlight issue

Posted in reply to srinidelite

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;

image.png

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;

image.png

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 165 views
  • 0 likes
  • 3 in conversation