PROC REPORT "COMPUTED conflicts with earlier use of XXXX"

[mg74]

Hi all,

I'm writing, for the first time, a "proc report" but... I have some problems.

The goal is to obtain a report in which I can calculate some column percentage, and formatting them comparing the row total. This is the code:

(NOTE: this proc is into a cycle, so &C1 is the counter)

proc report data=final_&C1 nowd missing;
column DEC AGEN,(v_TOT_CSZ_&C1 v_TOT_SSR N pct_col v_SSR) v_TOT_CSZ_&C1 v_TOT_SSR N pct_col v_SSR;
 define DEC             / group  order=internal " " width=20                                    ; /* C1        */
 define AGEN            / across order=internal " "                                             ;
 define v_TOT_CSZ_&C1   / analysis sum noprint  " "                                             ; /* C2  7  12 */
 define v_TOT_SSR       / analysis sum noprint  " "                                             ; /* C3  8  13 */
 define N               / noprint               "Fin"                   format=5.               ; /* C4  9  14 */
 define pct_col         / computed              '%prt fin'              format=percent6.2       ; /* C5  10 15 */
 define v_SSR           / analysis              '%sal_ssr'      mean    format=5.2              ; /* C6  11 16 */

compute before;
        den_fin1 = _C2_ ;
        den_fin2 = _C7_ ;
        den_fin3 = _C12_ ;

        num1 = _C3_ + _C8_ ;
        den1 = _C2_ + _C7_ ;
        tot_fin1 = num1 / den1 ;
endcompute;

compute pct_col;
        _C5_ = _C2_ / den_fin1;
        _C10_ = _C7_ / den_fin2;
        _C13_ = _C12_ / den_fin3;
        if _C5_ <  ( tot_fin*0.4 )      then call define ("_C5_", "style", "style=[background=CX00FF00]" );                     else
        if _C5_ >= ( tot_fin*2.7 )      then call define ("_C5_", "style", "style=[background=CXFF0000 font_weight=bold]" );
endcompute;

run;

but.... I receive the following error:

ERROR: COMPUTED conflicts with earlier use of pct_col

....help!!!!!! Where I fail?

Thanks in advance

[BrunoMueller]

If it is about a percentage calculation for an analysis variable, you can use the PCTSUM statistic keyword for a column.

Here is a sample code that illustrates that.

proc report data=sashelp.cars;
  column type origin, (invoice invoice=pctcol ) ("overall" invoice=invoice2 invoice=pctcol2);
  define type / group;
  define origin / across;
  
  define invoice / analysis sum format=nlnum14.0;
  define pctcol / analysis pctsum format=percent9.2 'in %';
  define invoice2 / analysis sum format=nlnum14.0;
  define pctcol2 / analysis pctsum format=percent9.2 'in %';

  rbreak after / summarize;
run;

Whether this does what you need, I can not say, but it might be a start.

Bruno

[mg74]

Hi Bruno, 

and thanks for the reply.

Now it works!!! The error disappear, so this is the solution 🙂

Thanks for your support

Now I have another problem. I think that it should be useful to open a new request but..., I try to explain here:

I have to change the color of the cell pct_col comparing that with the total pct_col in the row:

This is the output I would have to have:

... pct_col1 .... pct_col2 ... pct_col_TOT

...   0,15   ....    0,99  ...   0,52

...   0,62   ....    0,20  ...   0,43

...   0,77   ....    0,60  ...   0,69

And the goal is to color the pct_col cells using the following rule:

if pct_col1 < pct_col_tot * 0,80 => then turns in YELLOW

if pct_col1 > pct_col_tot * 1,20 => then turns in BLUE

but....

How I can estimate the pct_col_tot (the total "pctsum" in the analysis define) for each row?

I try this:

compute pct_col;
        if _C6_ not in( 0,. )
                then
                        do;
                                if _C6_ <  ( pct_col_tot*0.8 )
                                        then
                                                call define ("_C6_", "style", "style=[background=<yellow>" );
                                        else
                                if _C6_ >= ( pct_col_tot*1.2 )
                                        then
                                                call define ("_C6_", "style", "style=[background=<blue>" );
                        end;
        if _C11_ not in( 0,. )
                then
                        do;
                                if _C11_ <  ( tot_ssr*0.9 )
                                        then
                                                call define ("_C11_", "style", "style=[background=CX00FF00]" );
                                        else
                                if _C11_ >= ( tot_ssr*1.1 )
                                        then
                                                call define ("_C11_", "style", "style=[background=CXFF0000]" );
                        end;

endcompute;


compute before;
        num = _C3_ + _C8_ ;
        den = _C2_ + _C7_ ;
        pct_col_tot = num / den * 100;
endcompute;

but the calculation use the pct_col_tot of the report, not the row's one.

Any help?

Once again, thanks Bruno 🙂

[BrunoMueller]

To do stuff with each cell in a report, I recommend to add a dummy column at the very end of the column list. This column has the NOPRINT option so it will never show in the report. In the compute block for this column you have access to all the values that are to left of it, so you can control every "cell".

I have added some more code to the previous example to illustrate this.

ods escapechar="^";
proc report data=sashelp.cars;
  column
    type origin, (invoice invoice=pctcol )
    ("overall" invoice=invoice2 invoice=pctcol2) 
    _dummy
  ;
  define type / group;
  define origin / across;
  define invoice / analysis sum format=nlnum14.0;
  define pctcol / analysis pctsum format=percent9.2 'in %';
  define invoice2 / analysis sum format=nlnum14.0;
  define pctcol2 / analysis pctsum format=percent9.2 'in %';
  define _dummy / computed noprint;

  compute _dummy;

    /* array for columns to check against overall pct  */
    array xpct{*} _c3_ _c5_ _c7_;

    do i = 1 to dim(xpct);

      /* only do if we have a value and not in break line */
      if missing(xpct{i}) = 0 and missing(_break_) = 1 then do;

        /* check individual against "rowtotal" */
        if xpct{i} > pctcol2 then do;
          call define(vname(xpct{i}), "style", "style={background=cx4dbf81}");
        end;
        else do;
          call define(vname(xpct{i}), "style", "style={background=cx99293d}");
        end;
      end;
    end;
  endcomp;

  /* create legend */
  compute after / style={just=left foreground=cx000000 };
    length line1 line2 $ 132;
    line1 = "^{style[foreground=cx4dbf81]^{unicode 2588}} over overall average";
    line2 = "^{style[foreground=cx99293d]^{unicode 2588}} under overall average";
    l1 = length(line1);
    l2 = length(line2);
    line line1 $varying132. l1;
    line line2 $varying132. l2;
  endcomp;

  rbreak after / summarize;
run;

Bruno