Create a cross tab and highlight the difference

[Kayalvizhi]

Hi ,

I have Fields in a tables , which is supposed to have same content .If there any good deviation needs to be highlighted.

Example:

A B
S S
M M
M M
S S
L L
S S
S S
S S
M M
S S
S L
S L

Below is the Cross tab created by PROC FREQ , I need to highlight value if it is greater than Diagonal value.

Table of A by b
B Total
L M S
A
L Frequency 1 0 0 1
Percent 8.33 0 0 8.33
Row Pct 100 0 0
Col Pct 33.33 0 0
M Frequency 0 3 0 3
Percent 0 25 0 25
Row Pct 0 100 0
Col Pct 0 100 0
S Frequency 2 0 6 8
Percent 16.67 0 50 66.67
Row Pct 25 0 75
Col Pct 66.67 0 100

Total Frequency 3 3 6 12
Percent 25 25 50 100

[ChrisNZ]

Show us 1. some code and 2. some results.

1. I suppose you ran this?

proc freq; 
  tables A*B; 
run;.

2. I suppose you obtained this?

Frequency Percent Row Pct Col Pct

Table of a by b a b L M S Total L 1 8.33 100.00 33.33 0 0.00 0.00 0.00 0 0.00 0.00 0.00 1 8.33 M 0 0.00 0.00 0.00 3 25.00 100.00 100.00 0 0.00 0.00 0.00 3 25.00 S 2 16.67 25.00 66.67 0 0.00 0.00 0.00 6 50.00 75.00 100.00 8 66.67 Total 3 25.00 3 25.00 6 50.00 12 100.00

What do you want next?

[Kayalvizhi]

I want to highlight first column 3rd row square , as it shows some deviation from diagonal value , which is 6 in 3rd row 3rd column

[ChrisNZ]

How I'd do it:

- pre-run the calculations

- identify the values to highlight

- modify these values slightly for reporting, for example add 1e-12

- create a colour format for 2+1e-12

- use that format to change the colour.

The modified value still shows as 2 in the report, but can be formatted differently from other 2s that may be present.

There may be cleverer ways.

I've used this for proc report and proc tabulate. Not too sure how much proc freq's styles can be altered, so you may have to use a different proc for reporting..

[Kayalvizhi]

If there is same value in the diagonal ,then it would highlight the diagonal value. Which doesn't need

[RichardDeVen]

Proc FREQ does not have a syntax to conditionally style a cell of it's output based on values in other cells. 

Nor does TABULATE. 

Nor does PRINT.

Proc REPORT does have the COMPUTE block in which a CALL DEFINE can be conditionally executed to change the STYLE attribute.

Suppose you want to stick with Proc FREQ.  What can you do ?

• Capture ODS OUTPUT of the CrossTabFreqs
  • You will have all the data needed to reconstruct the crosstab rendering with custom styling. 
  • One row per combination of <var1>*<var2>
• Process the captured output
  • Compute a 'cell value' that stacks the frequencies in the same manner as Proc FREQ cells
    • Stacking can be done using inline styling function ^{NEWLINE} 
  • Tag the cells that are freq > diagonal freq
• Transpose the CrossTabFreq into a grid shape
• Proc REPORT the grid shaped data
  • Conditionally style the cell if the cell value is tagged

Example:

data have;
input A$ B$ @@;
datalines;
S S S S S S S S S S S S
M M M M M M
L L
L M L M L M
S L S L
S M S M S M S M S M S M S M
;

* CAPTURE CrossTabFreqs;

ods _all_ close;
ods output CrossTabFreqs=xtab;
proc freq data=have;
  tables A * B;
run;

* FLAG and STACK cross tab freq renders;
options missing=' ';
data xcells;
  if 0 then set xtab;

  * HASH for looking up diagonal freq;
  if _n_ = 1 then do;
    retain diagfreq .;
    declare hash diags(dataset:'xtab(where=(a=b and not missing(a)) rename=frequency=diagfreq)');
    diags.defineKey('a');
    diags.defineData('diagfreq');
    diags.defineDone();
  end;

  set xtab;

  length mlv $100;  * mlv ~ multi line value;

  rc = diags.find();

  * TAG mlv per OP criteria;
  if rc=0 and not missing(b) and DiagFreq < Frequency then do;
    mlv = '*';
  end;

  * STACK the count and percents for later output;
  mlv = trim(mlv) || vvalue(Frequency)
  || '^{NEWLINE}' || vvalue(Percent);

  if not missing(RowPercent) then mlv = trim(mlv)
  || '^{NEWLINE}' || vvalue(RowPercent);

  if not missing(ColPercent) then mlv = trim(mlv)
  || '^{NEWLINE}' || vvalue(ColPercent);

  if missing (A) then A = 'Total';
  if missing (B) then B = 'Total';
run;
options missing='.';

* RESHAPE to a grid;
proc transpose data=xcells out=grid(drop=_name_);
  by a notsorted;
  id b;
  var mlv;
run;

* Code generate macro calls for conditional styling the grid columns;
* Column names are from transpose id b, and thus the names are the values of b;
proc sql noprint;
  select distinct ('%compute('||cats(b)||')') into :columns separated by ' ' from xtab where not missing(b);
quit;

ods escapechar = '^';
ods html file='xtab.html';

* compile the macro that the codegen will invoke upon resolution;
%macro compute(column);
  compute &column.;
    if &column. =: '*' then do;
      &column. = left(substr(&column.,2));
      call define ("&column.", 'style', 'style=[background=orange]');
    end;
  endcomp;
%mend;

proc report data=grid style(column)=[textalign=right];
  columns ('B' _character_);
  define a / order order=data style=header;

  /* resolve the codegen so the macro calls further codegen COMPUTE block source codes that perform the conditional styling */
  &columns
run;

ods _all_ close;