I have coloured the second line (because it is not the first line for row R1).
I also want to colour the third line (because a count is equal to 3).
Any idea how? My attempts (commented out below) fail.
data HAVE;
input ROW $ COL $ VALUE $ ;
cards;
R1 C1 x
R1 C2 y
R1 C1 x1
R1 C2 y
R2 C1 x
R2 C2 z
R2 C1 x
R2 C2 z
R2 C2 z
run;
proc freq data=HAVE noprint;
table ROW*COL*VALUE/out=FREQ ;
run;
data LINED;
set FREQ;
by ROW COL;
if first.COL then LINE=0;
LINE+1;
run;
proc sort;
by ROW LINE COL;
run;
proc report data=LINED nowd ;
column ROW LINE COL,(VALUE COUNT);
define ROW /group;
define LINE /group noprint;
define COL /across ' ';
define VALUE /display ;
define COUNT /analysis '#' ;
compute LINE;
if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
* if COUNT=3 then call define(_row_,"style","style={background=cxEEFFFF}");
* if _C5_=3 | _C3_=3 then call define(_row_,"style","style={background=cxEEFFFF}");
endcomp;
run;
The timing and execution of the compute blocks can be quite different based on the association of report items. In this scenario you may want the compute block to execute when the proc report processes the COUNT column.
There are some good papers that go into quite a bit of detail to explain the sequence of compute blocks. From memory they work from left to right in order of columns (not by the order of your compute block codes).
Once I adjusted the compute block to execute when processing the COUNT column the issue with the IN operator is corrected. You could combine the LINE compute block with the COUNT compute block since LINE column would already be processed by this stage.
proc report data=LINED nowd out=t; column ROW LINE COL,(VALUE COUNT); define ROW /group; define LINE /group noprint; define COL /across ' '; define VALUE /display ; define COUNT /analysis '#' ; /* compute LINE;*/ /* if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");*/ /* endcomp;*/ compute COUNT; if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}"); * 3rd line is blue as intended; * if _C4_ = 2 then call define(_row_,"style","style={background=cxEEFFFF}"); * 1st line is blue as intended; * if _C4_ = 1 then call define(_row_,"style","style={background=cxEEFFFF}"); * all lines are blue! not intended ! ; * Corrected by adjusting the COMPUTE block to execute when processing of COUNT; if _C4_ ^in (1,2) then call define(_row_,"style","style={background=cxEEFFFF}"); endcomp; run;
Hi Chris
I'd generally recommend using OUT to create an output dataset during development. That way you can see the values created within the Proc Report step - sometimes its difficult to understand what is actually happenning in the procedure and having access to this information is useful during development.
This code should give you what your looking for.
Cheers
proc report data=LINED nowd out=work.test; column ROW LINE COL,(VALUE COUNT); define ROW /group; define LINE /group noprint; define COL /across ' '; define VALUE /display ; define COUNT /analysis '#' ; compute LINE; if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}"); * if COUNT=3 then call define(_row_,"style","style={background=cxEEFFFF}"); * if _C5_=3 | _C3_=3 then call define(_row_,"style","style={background=cxEEFFFF}"); endcomp; compute COL; if _C4_ = 3 or _C6_=3 then call define(_row_,"style","style={background=cxEEFFFF}"); endcomp; run;
Thanks for the out= tip!
2 things:
- why do we need to have column COL as the computed value?
- something seems wrong with inequalities:
proc report data=LINED nowd out=t;
column ROW LINE COL,(VALUE COUNT);
define ROW /group;
define LINE /group noprint;
define COL /across ' ';
define VALUE /display ;
define COUNT /analysis '#' ;
compute LINE;
if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");
endcomp;
compute COL;
* 3rd line is blue as intended;
* if _C4_ = 2 then call define(_row_,"style","style={background=cxEEFFFF}");
* 1st line is blue as intended;
* if _C4_ = 1 then call define(_row_,"style","style={background=cxEEFFFF}");
* all lines are blue! not intended ! ;
* if _C4_ ^in (1,2) then call define(_row_,"style","style={background=cxEEFFFF}");
endcomp;
run;
The timing and execution of the compute blocks can be quite different based on the association of report items. In this scenario you may want the compute block to execute when the proc report processes the COUNT column.
There are some good papers that go into quite a bit of detail to explain the sequence of compute blocks. From memory they work from left to right in order of columns (not by the order of your compute block codes).
Once I adjusted the compute block to execute when processing the COUNT column the issue with the IN operator is corrected. You could combine the LINE compute block with the COUNT compute block since LINE column would already be processed by this stage.
proc report data=LINED nowd out=t; column ROW LINE COL,(VALUE COUNT); define ROW /group; define LINE /group noprint; define COL /across ' '; define VALUE /display ; define COUNT /analysis '#' ; /* compute LINE;*/ /* if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}");*/ /* endcomp;*/ compute COUNT; if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}"); * 3rd line is blue as intended; * if _C4_ = 2 then call define(_row_,"style","style={background=cxEEFFFF}"); * 1st line is blue as intended; * if _C4_ = 1 then call define(_row_,"style","style={background=cxEEFFFF}"); * all lines are blue! not intended ! ; * Corrected by adjusting the COMPUTE block to execute when processing of COUNT; if _C4_ ^in (1,2) then call define(_row_,"style","style={background=cxEEFFFF}"); endcomp; run;
Put that in the right column. data HAVE; input ROW $ COL $ VALUE $ ; cards; R1 C1 x R1 C2 y R1 C1 x1 R1 C2 y R2 C1 x R2 C2 z R2 C1 x R2 C2 z R2 C2 z run; proc freq data=HAVE noprint; table ROW*COL*VALUE/out=FREQ ; run; data LINED; set FREQ; by ROW COL; if first.COL then LINE=0; LINE+1; run; proc sort; by ROW LINE COL; run; proc report data=LINED nowd ; column ROW LINE COL,(VALUE COUNT); define ROW /group; define LINE /group noprint; define COL /across ' '; define VALUE /display ; define COUNT /analysis '#' ; compute LINE; if LINE > 1 then call define(_row_,"style","style={background=cxEEFFFF}"); endcomp; compute count; if _C6_=3 | _C3_=3 then call define(_row_,"style","style={background=cxEEFFFF}"); endcomp; run;
Thank you all.
These proc report compute blocks sure are tricky to get right.
All good now i think!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.