BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ChrisNZ
Tourmaline | Level 20

 

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).

 

aaa1.PNG

 

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;

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Dan_B
Fluorite | Level 6

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;

View solution in original post

5 REPLIES 5
Dan_B
Fluorite | Level 6

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;

 

ChrisNZ
Tourmaline | Level 20

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;

 

Dan_B
Fluorite | Level 6

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;
Ksharp
Super User

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;
ChrisNZ
Tourmaline | Level 20

Thank you all.

These proc report compute blocks sure are tricky to get right.

All good now i think!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 11594 views
  • 3 likes
  • 3 in conversation