topic Re: PROC REPORT Conditional Formatting Issue in SAS Programming

PROC REPORT Conditional Formatting Issue

gdaymte — Fri, 13 Sep 2019 19:29:09 GMT

PROC REPORT DATA=WORK.A_EXTRACT;
	TITLE1 'LINE LIST OF DATA ENTRY EXCEPTIONS';
	TITLE2 "AS OF %QUPCASE(%LEFT(%QSYSFUNC(DATE(),WORDDATE18.)))";
	WHERE
		Year = 2019 AND
		CntInv = 1 AND
		(
			(
				(AgeCalcDy IS NULL AND AgeCalcMnth IS NULL AND AgeCalcYr IS NULL) OR
				(AgeRptdUnits = 'D' AND (AgeRptd NE AgeCalcDy)) OR
				(AgeRptdUnits = 'M' AND (AgeRptd NE AgeCalcMnth)) OR
				(AgeRptdUnits = 'Y' AND (AgeRptd NE AgeCalcYr)) 
			) OR
			(AgeRptd IS NULL) OR
			(AgeRptdUnits IS NULL) OR
			(DOB IS NULL) 
		)
	;
	COLUMNS
		PersonID
		LocalID
		DiseaseRepCat
		AgeCalcDy
		AgeCalcMnth
		AgeCalcYr
		AgeRptd
		AgeRptdUnits
		DOB
	;
	DEFINE PersonID / ORDER;
	DEFINE LocalID / ORDER;
	DEFINE DiseaseRepCat / ORDER;
	DEFINE AgeCalcDy / DISPLAY;
	DEFINE AgeCalcMnth / DISPLAY;
	DEFINE AgeCalcYr / DISPLAY;
	DEFINE AgeRptd / DISPLAY;
	DEFINE AgeRptdUnits / DISPLAY;
	DEFINE DOB / DISPLAY;
	
	COMPUTE AgeCalcDy;
		IF AgeRptdUnits = 'D' AND (AgeRptd NE AgeCalcDy) THEN
			CALL DEFINE(_COL_,"STYLE","STYLE={BACKGROUNDCOLOR=YELLOW}");
	ENDCOMP;
	COMPUTE AgeCalcMnth;
		IF AgeRptdUnits = 'M' AND (AgeRptd NE AgeCalcMnth) THEN
			CALL DEFINE(_COL_,"STYLE","STYLE={BACKGROUNDCOLOR=YELLOW}");
	ENDCOMP;
	COMPUTE AgeCalcYr;
		IF AgeRptdUnits = 'Y' AND (AgeRptd NE AgeCalcYr) THEN
			CALL DEFINE(_COL_,"STYLE","STYLE={BACKGROUNDCOLOR=YELLOW}");
	ENDCOMP;
	COMPUTE AgeRptd;
		IF AgeRptd=. THEN
			CALL DEFINE(_COL_,"STYLE","STYLE={BACKGROUNDCOLOR=YELLOW}");
	ENDCOMP;
	COMPUTE AgeRptdUnits;
		IF AgeRptdUnits='' THEN
			CALL DEFINE(_COL_,"STYLE","STYLE={BACKGROUNDCOLOR=YELLOW}");
	ENDCOMP;
	COMPUTE DOB;
		IF DOB=. THEN
			CALL DEFINE(_COL_,"STYLE","STYLE={BACKGROUNDCOLOR=YELLOW}");
	ENDCOMP;
RUN;

My conditional highlighting works for AgeRptd, AgeRptdUnits, and DOB...the fields with a single condition to meet. What am I doing wrong for AgeCalcDy, AgeCalcMnth, and AgeCalcYr? Is it not possible to require two conditions to be met if you want to conditionally hightlight something?

Re: PROC REPORT Conditional Formatting Issue

ballardw — Fri, 13 Sep 2019 22:29:17 GMT

Proc Report builds the output from left to right and top to bottom. If a Compute block references a variable that appears to the right in the columns position of that value then it doesn't "see" the values. So they can't be used to format those cells.

AgeCalcDy appears before AgeRptdUnits in your Columns statement, so that is what doesn't happen.

Similar with the other problem variables.

Simplest would be change the order of variables in the Column statement.

Realizing that one reason you may be doing the report is to match a required layout then one solution is to create a copy of your problem variable in your data set such as AgeDummy, add it to the Columns statement before the columns that will use the values. Then have

Define AgeDummy / noprint;

to suppress the values from actually appearing in the report.

And use AgeDummy in the compute block instead.

Re: PROC REPORT Conditional Formatting Issue

Cynthia_sas — Fri, 13 Sep 2019 23:32:08 GMT

Hi:

Your problem is the fact that PROC REPORT has a "left to right" rule for what you can reference in a COMPUTE block. The issue is not your compound condition, the issue is as shown in the slightly rearranged code below:

To go by the color coding in the rearranged COLUMN statement. In the "green" COMPUTE blocks you are trying to reference the yellow and pink variables. This won't work. PROC REPORT does not have a PDV like the DATA step. PROC REPORT writes one report row at a time, working from left-to-right based on the order of the variables in the COLUMN statement.

At the point in time when your AgeCalcDy item is written to the report row, the yellow and pink columns have not been written yet. At this point in time, PROC REPORT only knows what the values are for PersonID LocalID DiseaseRepCat and AgeCalcDy. Then, when AgeCalcMnth is being placed on the report row, the yellow and pink columns still have not been written yet. At that point in time, PROC REPORT only knows what the values are for PersonID LocalID DiseaseRepCat AgeCalcDy and AgeCalcMnth. When PROC REPORT gets to the last column on the row (in this case, DOB), that's when it has visibility of all the other values on the report row.

Here's a good posting from the Communities on the topic https://communities.sas.com/t5/General-SAS-Programming/PROC-REPORT-compare-col-vs-col/td-p/368228

Hope this helps explain your issue.

Cynthia

PS if you're going to post code, it's also nice to post some sample data or use one of the SASHELP datasets to illustrate the issue.