Help using Base SAS procedures

I want to conditionally highlight the cell.

Reply
Contributor sss
Contributor
Posts: 65

I want to conditionally highlight the cell.

============================================================
ANUM JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOTAL_FAIL
============================================================
4299 40 20 27 37 25 32 26 24 22 28 17 33 27.58
4264 30 30 36 15 17 27 27 30 25 27 14 24 25.17
4246 19 24 40 18 32 18 20 19 24 16 14 25 22.42
4281 16 18 31 23 17 24 27 20 19 26 13 27 21.75
41786 21 43 33 21 20 21 26 21 11 11 13 14 21.25
4080 16 19 21 16 13 23 13 18 21 17 27 20 18.67
4262 17 14 23 16 30 22 20 18 26 11 9 10 18.00
1789 19 19 14 10 21 19 24 17 18 24 9 14 17.33
4315 20 11 28 17 18 19 11 22 17 25 5 11 17.00
4297 16 11 17 15 14 14 15 16 28 12 6 26 15.83
4279 17 15 16 11 14 14 8 15 14 15 8 8 12.92
4042 15 6 8 6 3 9 13 19 8 3 5 10 8.75
41787 4 4 9 9 3 8 8 19 16 8 10 7 8.75
2377 10 6 11 4 13 11 4 8 9 4 4 7 7.58
4241 6 5 9 5 7 7 6 12 6 7 3 4 6.42
============================================================
hi.......
I want to conditionally highlight the cell.
In col(ANUM) is number for asset and values in MONTH(Jan,Feb....DEC) column are No. of Failures with respective to month and TOTAL_FAIL in mean on all month.

I want to highlight the values whose values are greater then TOTAL_FAIL With respect to ANUM col as Red or some other color.

ex:-in 1st row i want to highlight the values 40,37,32,28,33
in 2nd row i want to highlight the values 30,30,30,27,27,27

This code is not working gr8 as per ma requirement
compute JAN ;
if JAN OR FEB OR MAR OR APR GE TOTAL_FAILURES then do;
call define(_COL_,'STYLE','STYLE=[foreground=red]');
end;
else
call define(_COL_,'STYLE','STYLE=[foreground=black]');

end comp;
run;


I 'll be very thankful for you suggestion
SAS Super FREQ
Posts: 8,743

Re: I want to conditionally highlight the cell.

Hi:
I see several problems:
1) Your IF statement might need some work. This is not correct syntax:
[pre]
if JAN OR FEB OR MAR OR APR GE TOTAL_FAILURES then do;
... more code ...
[/pre]

In regular DATA step coding, you must have complete expressions joined by OR, such as (your code, BTW, shows the variable is TOTAL_FAIL and not TOTAL_FAILURES):
[pre]
if JAN GE TOTAL_FAIL OR
FEB GE TOTAL_FAIL OR
MAR GT TOTAL_FAIL OR
APR GE TOTAL_FAIL then do;
... more code ...
[/pre]

However, that is in a DATA step program. Inside PROC REPORT, you are constrained about which report items you can use in a COMPUTE block...which leads to #2:

2) If this is your COLUMN statement
[pre]
COLUMN ANUM JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOTAL_FAIL;
[/pre]

Then, at the point where JAN is available to the COMPUTE block, TOTAL_FAIL has not been put on the report row yet, so it is not available for a comparison. In the COMPUTE block for JAN (if the above IS your column statement), the only other report item that you could use in the COMPUTE block for JAN is the ANUM report item (because the COMPUTE block places items on the report row from LEFT to RIGHT -- and only items to the left of JAN (in this case, ANUM) are available for an IF statement. In fact, FEB, MAR, APR, MAY, etc are all to the RIGHT of JAN, so they would not be available for an IF statement.

3) If #2 is NOT your COLUMN statement, but you are instead using an ACROSS report item, then you would still have the Left to Right column issue AND you would have to use absolute column numbers for each month....like _C2_ would be JAN, _C3_ would be FEB, etc.

As you can see, the answer depends on your EXACT PROC REPORT code and all of your PROC REPORT code. Also, you do not say what your destination of interest is. Conditional highlighting will only work in ODS destinations that support style -- if you hope to see highlighting in the LISTING destination, then the CALL DEFINE will not work in LISTING.

Can you post ALL of your code, including the ODS statements.

In the meantime, I know that there have been previous forum postings on the use of CALL DEFINE to conditional highlighting -- both with and without ACROSS -- so you should be able to search the forum and find some concrete examples.

cynthia
Contributor sss
Contributor
Posts: 65

Re: I want to conditionally highlight the cell.

Hi
@cynthia

I am very few to SAS programming n to this forum. Sorry for posting multiple times

Yes what ever code i have posted its logic is wrong . I hope you have got my problem defination ,can u suggest me the current logic
Frequent Contributor
Posts: 75

Re: I want to conditionally highlight the cell.

Hi,

Here is one possible solution for your problem. Try to customize based on your requirement.

data temp;
infile datalines;
input ANUM JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOTAL_FAIL 5.2;
datalines;
4299 40 20 27 37 25 32 26 24 22 28 17 33 27.58
4264 30 30 36 15 17 27 27 30 25 27 14 24 25.17
4246 19 24 40 18 32 18 20 19 24 16 14 25 22.42
4281 16 18 31 23 17 24 27 20 19 26 13 27 21.75
41786 21 43 33 21 20 21 26 21 11 11 13 14 21.25
4080 16 19 21 16 13 23 13 18 21 17 27 20 18.67
4262 17 14 23 16 30 22 20 18 26 11 9 10 18.00
1789 19 19 14 10 21 19 24 17 18 24 9 14 17.33
4315 20 11 28 17 18 19 11 22 17 25 5 11 17.00
4297 16 11 17 15 14 14 15 16 28 12 6 26 15.83
4279 17 15 16 11 14 14 8 15 14 15 8 8 12.92
4042 15 6 8 6 3 9 13 19 8 3 5 10 8.75
41787 4 4 9 9 3 8 8 19 16 8 10 7 8.75
2377 10 6 11 4 13 11 4 8 9 4 4 7 7.58
4241 6 5 9 5 7 7 6 12 6 7 3 4 6.42
;
run;

proc report data=temp nowd;
column ANUM TOTAL_FAIL JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ;
define ANUM /display;
define JAN / display;
define FEB /display;
define MAR /display;
define APR /display;
define MAY /display;
define JUN /display;
define JUL /display;
define AUG /display;
define SEP /display;
define OCT /display;
define NOV /display;
define DEC /display;
define TOTAL_FAIL /display;
compute JAN;
if JAN > TOTAL_FAIL then
call define(_col_,'style','style={foreground=red}');
endcomp;
compute FEB;
if FEB > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute MAR;
if MAR > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute APR;
if APR > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute MAY;
if MAY > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute JUN;
if JUN > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute JUL;
if JUL > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute AUG;
if AUG > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute SEP;
if SEP > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute OCT;
if OCT > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute NOV;
if NOV > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;
compute DEC;
if DEC > TOTAL_FAIL then
call define(_col_,"style","style={foreground=red}");
endcomp;

run;

Thanks

Dhanasekaran R
Allianz Cornhill Information Services,
Trivandrum,
India
Contributor sss
Contributor
Posts: 65

Re: I want to conditionally highlight the cell.

Thnx u dhana


i got the output
Ask a Question
Discussion stats
  • 4 replies
  • 146 views
  • 0 likes
  • 3 in conversation