Proc Report Computed column

[ECrecelius]

Hello all,

I am trying to create a computed column in my proc report to highlight when a certain condition is true. Here is what I have:

define yellowFlag / NOPRINT;

Compute yellowflag;

if Visit eq 'Follow-Up - Day 3 (+/-24h) (1)' and ActualVW not in (2,3,4) then do;
call define(_row_,'style', 'style=[background=mistyrose]');

end;

end comp;

When I run this, it comes back highlighted for all when visit eq 'Follow-Up - Day 3 (+/-24h) (1)'. 

[PaigeMiller]

Show us the entire PROC REPORT.

Show us a portion of the data.

[ECrecelius]

proc report data = out.visitwindow11;

column SubID Visit Visitdate AdminD ActualVW yellowflag;

define Subid / "Subject";

define AdminD / "Drug Admin Date";

define ActualVW / "Actual Visit Window";

define yellowFlag / NOPRINT;

Compute yellowflag;

if Visit eq 'Follow-Up - Day 1 (+/-4h) (1)' and ActualVW > 1 then do;

call define(_row_,'style', 'style=[background=mistyrose]');

end;

if Visit eq 'Follow-Up - Day 3 (+/-24h) (1)' and ActualVW not in (2,3,4) then do;

call define(_row_,'style', 'style=[background=mistyrose]');

end;

if Visit eq 'Follow-Up - Day 6 (+/-24h) (1)' and (ActualVW) not in (5,6,7) then do;

call define(_row_,'style', 'style=[background=mistyrose]');

end;

if Visit eq 'Screening (-30 to -1)' and ActualVW > -30 then do;

call define(_row_,'style', 'style=[background=mistyrose]');

end;

if Visit eq 'Follow-Up - Day 28 (+/- 48h) (1)' and (ActualVW) not in (26,27,28,29,30) then do;

call define(_row_,'style', 'style=[background=mistyrose]');

end;

endcomp;

　

run;

The follow up visits are all highlighted.

[PaigeMiller]

Show us a portion of the data.

[ECrecelius]

data visit;

input ID $7 AdminD date9. Visit  $40 VisitDate date9. ActualVW 8.;

101-001 21NOV2019 Follow-Up - Day 3 (+/-24h) (1) 25NOV2019 4

101-001 21NOV2019 Follow-Up - Day 6 (+/-24h) (1) 27NOV2019 6

I calculated ActualVW :

data out.VisitWindow11;

set out.visitwindowtest2;

ActualVW= VisitDate-AdminD;

run;

[PaigeMiller]

When I try to re-create your data set VISIT from the code above, there are loads of errors in the log. So please provide working code to create the data.

[newfee]

Hi, 

if I have code like yours, it always colors the whole row, not just certain cells.

The way around this, that I use, is something like this:

I first establish a format (proc format; value $colors ‘1’ = ‘blue’   ‘2’ = ‘red’; run;)

Then within the proc report: 

define myvar / display style(column)=[backgroundcolor=$colors.];

compute myvar;

   call define(_col_,”style/merge”,”style=[font=(arial,9pt)]”);

endcomp;

I hope this is understandable.

[ECrecelius]

I am able to highlight the whole row. The problem is something to do with the if then do :

if Visit eq 'Follow-Up - Day 3 (+/-24h) (1)' and ActualVW not in (2,3,4) then do;
call define(_row_,'style', 'style=[background=mistyrose]');

It will highlight every row that has the visit equal to what is above regardless of the ActualVW not equaling a 2,3, or 4. I'm wondering if it has to do with the format of the ActualVW variable which is calculated between 2 dates.

[newfee]

Do I understand correctly that you want to only highlight certain cells?

[ECrecelius]

No, I want to highlight the whole row by two conditions: if it is a certain visit, and when the ActualVW doesn't eq (2,3,4) but it highlights the rows if the first condition is met regardless of the second condition.

[Cynthia_sas · Accepted solution]

Hi:

  Well, once you fix the data and get the code working, using _ROW_ in the Call Define is going to highlight the whole row, as shown below using changed data:

  It's not clear to me whether one cell was being highlighted or the rows. I dummied up some test data, that tested all 5 IF statements on the True branch and the False branch using a different color for each IF. I also changed the PROC REPORT code to explicitly use DISPLAY for all variables and to use ELSE IF since there's no point continuing to check VISIT in each IF after the condition has been met. I also assigned a value to the YELLOWFLAG helper variable just to double check my logic and make sure that each IF was getting hit by the data. NOPRINT can be applied to YELLOWFLAG after the logic is working.

  Here's the code I used to make my test data. Once the OP clarifies what it is that is needed, this test data will hit all 5 IF statements.

data visit;
  length ID $7 Visit $40;
infile datalines dlm=',' dsd;
input ID $ AdminD : date. Visit $ VisitDate : date. ActualVW ;
datalines;
tst-if1,21NOV2019,"Follow-Up - Day 1 (+/-4h) (1)",25NOV2019,1
tst-if1,21NOV2019,"Follow-Up - Day 1 (+/-4h) (1)",25NOV2019,2
tst-if2,21NOV2019,"Follow-Up - Day 3 (+/-24h) (1)",25NOV2019,4
tst-if2,21NOV2019,"Follow-Up - Day 3 (+/-24h) (1)",25NOV2019,5
tst-if3,21NOV2019,"Follow-Up - Day 6 (+/-24h) (1)",27NOV2019,6
tst-if3,21NOV2019,"Follow-Up - Day 6 (+/-24h) (1)",27NOV2019,8
tst-if4,21NOV2019,"Screening (-30 to -1)",27Nov2019,-35
tst-if4,21NOV2019,"Screening (-30 to -1)",27Nov2019,-1
tst-if5,21NOV2019,"Follow-Up - Day 28 (+/- 48h) (1)",27Nov2019,27
tst-if5,21NOV2019,"Follow-Up - Day 28 (+/- 48h) (1)",27Nov2019,25
;
run;

Cynthia

[ECrecelius]

I tried that and it still highlights rows when it should not based on the first condition. It ignores the second condition with the ActualVW variable. It is a numeric variable.

[Cynthia_sas]

Hi:

  Did you notice the difference between the DEFINE statement you showed for ActualVW from the unsuccessful code:

define ActualVW / "Actual Visit Window";

and the DEFINE statement I used in the successful code:

define ActualVW / display "Actual Visit Window";

Without a usage of DISPLAY, you would have to change ALL your IF statements to refer to ActualVW.sum in your COMPUTE block. That's because for numeric items, PROC REPORT expect numeric analysis variables to be referenced by a compound name (variablename.statistic) which for your original code would have been ActualVW.sum  -- that's why I changed the usage of ActualVW in my code. Otherwise, if you don't you will get this Note in the LOG from PROC REPORT:

NOTE: Variable ActualVW is uninitialized.

You may not have noticed it since it is not a warning or an error message. My test cases in my fake data did get past the first IF statement. As you can see in my screen shot, my data and my code tested all 5 IF conditions on the true branch and the false branch.

Hope this helps,

Cynthia

[ECrecelius]

This worked! Thank you