|
|
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
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?
|
|
What do you want next?
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..
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 ?
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;
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.