PROC REPORT compare col vs col

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

PROC REPORT compare col vs col

Hi There ,

 

What makes the following code to highlight in my report though it matches when i do eyeball verification ..

 

I.e I'm expecting my code perform  to col vs col comparison on row value and highlight incase of mismatch but its not happening

 

Can you help me out in what's wrong with my code pls

 

 

 

COMPUTE AMTPRE;
 IF AMTPRE not eq AMTPRE96 
THEN DO ;
 call define (_col_,"style","style={background= orange}");
 END;
 ENDCOMP;
 COMPUTE AMTEXTFORPAYPERI;
  IF AMTEXTFORPAYPERI  not eq AMTE4PAPE96 
THEN DO ;

 

I'm Using PRO_REPORT to display the result s

 

PRE COMP.png

Your Help is Much Appriciated 


Accepted Solutions
Solution
‎06-21-2017 02:06 AM
SAS Super FREQ
Posts: 8,721

Re: PROC REPORT compare col vs col

Hi:
Thanks for posting your code. All of my comments are still relevant. You CANNOT test both AMTPRE and AMTPRE_1096 in the COMPUTE block for AMTPRE because of the the "left to right" rule of PROC REPORT.

cynthia

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,698

Re: PROC REPORT compare col vs col

Depending on how your variables are calculated, you might run into numeric precision issues that cause differences where there should be none.

Use the round() function to remove any artifacts that hide far behind the decimal comma.

For better readability, I would also replace "not eq" with the simpler mnemonic "ne".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 17

Re: PROC REPORT compare col vs col

[ Edited ]

Thanks Kurt  Smiley Happy

 

However both the suggestions has no effect on the report !

 

COMPUTE AMTPRE;
 IF round(AMTPRE,.02) ne round(AMTPRE_1096 ,.02)
THEN DO ;
 call define (_col_,"style","style={background= orange}");
 END;
 ENDCOMP;

 

Occasional Contributor
Posts: 17

Re: PROC REPORT compare col vs col

im certainly thinking it highlights the column completely however it should be a cell to be highlighted
SAS Super FREQ
Posts: 8,721

Re: PROC REPORT compare col vs col

[ Edited ]

Hi:
How are AMTPRE and AMTPRE96 (and other variables) used on the report. By that I mean are you specifically listing them in a DEFINE statement with a usage of DISPLAY? Or are you taking the default usage of SUM?

What does your COLUMN statement look like? For example, you need to remember the "left to right" rule of PROC REPORT. PROC REPORT does not work like the DATA step. There is NO Program Data Vector. PROC REPORT can only "see" what has been placed on the report row, working from left to right.

Here is an example of what I mean:
proc report data=fakedata;
  column var1 var2 var3 var4;
  define var1 / display;
  define var2 / display;
  define var3 / sum;
  define var4 / sum;
  compute var1;
    if var1 ne var2 then do;
      call define (....);
    end;
  endcomp;
  compute var4;
    if var3.sum ne var4.sum then do;
       call define (....);
    end;
  endcomp;
run;

The COMPUTE block for VAR1 is WRONG because of the left to right rule. At the point in time when PROC REPORT is writing VAR1 on the report, it has not yet written VAR2, so even though the comparison in the IF is correct for DISPLAY variables, the result will ALWAYS be false because at this point in time even if VAR1 has a value, VAR2 is missing on the report row because it hasn't been written yet. So the IF will ALWAYS be true.

As shown in the COMPUTE block for VAR4, you can test any item on the REPORT row, that is to the left of VAR4. So, although a COMPUTE block for VAR3 could NOT test VAR4, a COMPUTE block for VAR4 could test VAR3.

PROC REPORT ALWAYS moves from left to right, placing items on the report row one by one. So it is a timing and placement issue.

Then there are just a few other things to remember: for numeric variables that are NOT GROUP, ORDER or DISPLAY usage, you MUST use the construct: variable_name.statistic (as in VAR3.sum or VAR4.sum) and the syntax in the call define will need to be different. If you want to "touch" VAR3 in the COMPUTE block for VAR4, then the CALL DEFINE has to be like this:
compute var4;
  if var3.sum ne var4.sum then do;
    call define ('var3.sum','style','style={background=orange}');
  end;
endcomp;

Notice how instead of _COL_, you need to use the item reference in quotes if you want to impact an item that is to the left of the item in the COMPUTE block.

cynthia

Occasional Contributor
Posts: 17

Re: PROC REPORT compare col vs col

Hi ,

 

Thanks for the details reply ..here is my code  ( not the complete one though )

 

ods html file ='/fakloc/myhtm.htm.';
PROC REPORT DATA=PRE_MISMATCH NOWD list ls=100;
COL PRI_KEY  
AMTPRE  AMTPRE_1096  
AMTEXTFORPAYPERI  AMTE4PAPE_1096    ;
title "Comparison  Report";
Define  PRI_KEY   / display ;
Define  AMTPRE   /  display	;
Define  AMTPRE_1096   /  display;
Define  AMTEXTFORPAYPERI   /  display	;
Define  AMTE4PAPE_1096   /  display;


COMPUTE AMTPRE;
/* Though i have rounded it to 2 decimal on my mismatch dataset itself however to ensure i'm using it again */
 IF round(AMTPRE,.02) ne round(AMTPRE_1096 ,.02)
THEN DO ;
 call define (_col_,"style","style={background= orange}");
 END;
 ENDCOMP;
 COMPUTE AMTEXTFORPAYPERI;
  IF AMTEXTFORPAYPERI  ne AMTE4PAPE_1096 
THEN DO ;
 CALL DEFINE(_col_,'style','style={background= orange}');
 END;
ENDCOMP;
 
RUN;
ods html close;
Solution
‎06-21-2017 02:06 AM
SAS Super FREQ
Posts: 8,721

Re: PROC REPORT compare col vs col

Hi:
Thanks for posting your code. All of my comments are still relevant. You CANNOT test both AMTPRE and AMTPRE_1096 in the COMPUTE block for AMTPRE because of the the "left to right" rule of PROC REPORT.

cynthia
Occasional Contributor
Posts: 17

Re: PROC REPORT compare col vs col

Hi ,

 

With the similar level of Question  , 

 

My code Highlights the column AMTPRE_1093  when ever there is mismatch however  the column AMTPRE doesn't perform what is expected .. can you please let me know ,,how to sort this out ?

 

 

PROC REPORT DATA=PRE_MISMATCH NOWD ;
	COL CDNUMPOL  
		AMTPRE  AMTPRE_1093 ; 
	
	title "Sample Mismatch Report";
	Define  CDNUMPOL   / display;
	Define  AMTPRE   /  display 'AMTPRE_SRC' ;
	Define  AMTPRE_1093   /  display 'AMTPRE_TRGT' ;

 

 

 

	COMPUTE AMTPRE_1093;

		IF AMTPRE ne AMTPRE_1093 THEN
			DO;
				call define (_col_,"style","style={background= orange}");
			END;
	ENDCOMP;
	COMPUTE AMTPRE;

		IF AMTPRE_1093 ne AMTPRE THEN
			DO;
				call define (_col_,"style","style={background= Blue}");
			END;
	ENDCOMP;

 

Occasional Contributor
Posts: 17

Re: PROC REPORT compare col vs col

I Understand it works on Left to Right .. However if i want to compare my right with Left again , What i suppose to do ?

 

Pls suggest me the Changes 

Esteemed Advisor
Posts: 6,698

Re: PROC REPORT compare col vs col

It seems to depend on the existence of a column. Computing a new one does the trick:

data test;
x1 = 1;
x2 = 2;
output;
x1 = 1;
x2 = 1;
output;
run;

proc report data=test;
column x1 x2 x3;
define x1 / display noprint;
define x2 / display;
compute x3;
  if x1 ne x2
  then call define(_col_,"style","style={background=orange}");
  x3 = x1;
endcomp;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Super FREQ
Posts: 8,721

Re: PROC REPORT compare col vs col

[ Edited ]

Hi:
You can always "impact" x1 and x2 in the COMPUTE block for x3 because at the point in time when X3 is being placed on the report, PROC REPORT has full visibility of X1 and X2. Your code would also have worked if you did this:

data test;
  infile datalines;
  input x1 x2 x3 x4;
return;
datalines;
1 2 3 4
1 1 4 4
5 6 7 7
;
run;

proc report data=test;
column x1 x2 x3 x4;
define x1 / display;
define x2 / display;
define x3 / sum;
define x4 / sum;
compute x2;
  if x1 ne x2
    then call define(_col_,"style","style={background=orange}");
endcomp;
compute x4;
  if x3.sum ne x4.sum
    then call define('x3.sum',"style","style={background=orange}");
endcomp;
run;


cynthia

Esteemed Advisor
Posts: 6,698

Re: PROC REPORT compare col vs col

I think I get it. But is it possible to let the variables appear in the sequence x1 x2 and set the style for x1 depending on x2 (or a comparison of x2 and x1)?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
SAS Super FREQ
Posts: 8,721

Re: PROC REPORT compare col vs col

Hi, Kurt, yes it is. Look at my compute block for X4, it is altering the style for X3 in the CALL DEFINE.

In PROC REPORT, you can test and/or change the style of the items on the report row, but since PROC REPORT puts the items down working from left to right, you can only compare X3 and X4 in the COMPUTE block for X4 and in the compute block for X4, you can "touch" the style of X3, as I show.

cynthia
Grand Advisor
Posts: 9,593

Re: PROC REPORT compare col vs col

 Make sure AMTPRE is after AMTPRE96  .   AMTEXTFORPAYPERI  is after AMTE4PAPE96  
Occasional Contributor
Posts: 17

Re: PROC REPORT compare col vs col

Thanks you .. It works Now ,,
☑ This topic is SOLVED.

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

Discussion stats
  • 15 replies
  • 225 views
  • 8 likes
  • 5 in conversation