topic Re: Traffic lighting in proc report not as expected in SAS Programming

Traffic lighting in proc report not as expected

vegan_renegade — Tue, 20 Aug 2024 23:31:03 GMT

HI all, not sure where I have gone wrong with this. I have an actual code, but created this dummy code, and the same issue occurs. Note that I'm asking cells to be red if the test date is missing (.) AND test result is not blank. However, I'm getting red cells in patients 1, 2 even though they have a test date. Based on this code, I should only have a red cell at test result for patient 5. What am I missing?

data test;
   input patient_id $ test_date :mmddyy10. test_result $15.;
   format test_date mmddyy10.;
   datalines;
1 06/01/2023 Negative
2 07/13/2023 Positive
3 09/25/2023 .
4 11/21/2023 .
5 . Positive
;
run;


proc report data=test;
compute test_result;
  if test_date = . and test_result ne ''
  then call define(_col_,'style','style={background=red}');
endcomp;
run;

Re: Traffic lighting in proc report not as expected

Tom — Wed, 21 Aug 2024 00:09:17 GMT

If you want to reference TEST_DATE in the compute block code you need to either DEFINE TEST_DATE as DISPLAY instead of the default of SUM. Or reference it as TEST_DATE.SUM (or some other statistic).

Re: Traffic lighting in proc report not as expected

vegan_renegade — Wed, 21 Aug 2024 15:59:55 GMT

Thank you! I changed the test code a bit to account for other scenarios and using "in" instead of "ne" to spell out the inclusive responses instead, and ran into a similar issue, even when using both variables as DISPLAY:

data test;
   input patient_id $ test_date :mmddyy10. test_result $15.;
   format test_date mmddyy10.;
   datalines;
1 . Not Done
2 . Positive
3 09/25/2023 .
4 07/13/2023 Positive
;
run;

proc report data=test;
define test_date / Display "Test Date";
define test_result / Display "Test Result";

compute test_date;
  if test_date = . and test_result in ('Positive' 'Negative' '')
  then call define(_col_,'style','style={background=red}');
endcomp;

compute test_result;
  if (test_result in ('Positive' 'Negative' '') and test_date = .)
  	or (test_result in ('Not Done' '') and test_date ne .)
  then call define(_col_,'style','style={background=red}');
endcomp;
run;

Patient 1: Test Date cell should not be red

Patient 2: Test Result cell should not be red

Re: Traffic lighting in proc report not as expected

ballardw — Wed, 21 Aug 2024 16:44:18 GMT

@vegan_renegade wrote:

Thank you! I changed the test code a bit to account for other scenarios and using "in" instead of "ne" to spell out the inclusive responses instead, and ran into a similar issue, even when using both variables as DISPLAY:
data test;
   input patient_id $ test_date :mmddyy10. test_result $15.;
   format test_date mmddyy10.;
   datalines;
1 . Not Done
2 . Positive
3 09/25/2023 .
4 07/13/2023 Positive
;
run;

proc report data=test;
define test_date / Display "Test Date";
define test_result / Display "Test Result";

compute test_date;
  if test_date = . and test_result in ('Positive' 'Negative' '')
  then call define(_col_,'style','style={background=red}');
endcomp;

compute test_result;
  if (test_result in ('Positive' 'Negative' '') and test_date = .)
  	or (test_result in ('Not Done' '') and test_date ne .)
  then call define(_col_,'style','style={background=red}');
endcomp;
run;
Patient 1: Test Date cell should not be red

Patient 2: Test Result cell should not be red

Why shouldn't Patient 2 result be red? The code says if the values is Positive and test_date is missing which are both the case for Patient 2.

Re: Traffic lighting in proc report not as expected

Tom — Wed, 21 Aug 2024 17:00:52 GMT

You are asking the COMPUTE block to look into the future. And since SAS has not yet released their psychic features that cannot work.

proc report data=test;
  column patient_id test_result test_date test_result=tr2 ;
define test_date / Display "Test Date";
define test_result / noprint ;
define tr2 / Display "Test Result";

compute test_date;
  if test_date = . and test_result in ('Positive' 'Negative' '')
  then call define(_col_,'style','style={background=red}');
endcomp;

compute tr2;
   if (test_result in ('Positive' 'Negative') and missing(test_date))
   or (test_result in ('Not Done' ' ') and not missing(test_date))
  then call define(_col_,'style','style={background=red}');
endcomp;
run;

Re: Traffic lighting in proc report not as expected

vegan_renegade — Wed, 21 Aug 2024 17:12:49 GMT

There is no need for an unhelpful and condescending response.

Re: Traffic lighting in proc report not as expected

vegan_renegade — Wed, 21 Aug 2024 17:21:45 GMT

Haha you're right. I guess I'm left with the issue on Patient # 1 only. Any clue on that one?

Re: Traffic lighting in proc report not as expected

Tom — Wed, 21 Aug 2024 17:45:20 GMT

@vegan_renegade wrote:
There is no need for an unhelpful and condescending response.

Huh?

Did you try making sure that TEST_RESULT appears in the COLUMN statement BEFORE the variable where you are trying to use its value in the COMPUTE block? Or not?

Re: Traffic lighting in proc report not as expected

ballardw — Wed, 21 Aug 2024 17:55:48 GMT

@vegan_renegade wrote:
There is no need for an unhelpful and condescending response.

Perhaps not, but there a details on how Proc Report uses things. For example from http://support.sas.com/resources/papers/proceedings15/SAS1642-2015.pdf which is referenced in my version of the online help for Proc Report under the Concepts tab in the Using Compute Blocks.

LESSON 1: THE COMPUTE STATEMENT
PROC REPORT processes a data set by reading the variables in the order in which they appear from left
to right in the COLUMN statement. The procedure builds the report one column and one row at a time,
and COMPUTE statements are executed as the report is built.

I emphasized the left to right because that is the order things work. A compute block cannot use a column to the right of its position. If you want Test_date to use Test_result directly it has to be to the right of Test_result. Since you have not provided a COLUMNS statement, then the order is the default from the data set and Test_date is LEFT of test_result. So doesn't "see" the Test_result values.

By creating the Alias for Test_date on a Columns statement that is to the right of Test_result you get around that limitation.

While not required a Columns statement is a good idea just so you keep in mind the relative positions of the output. Also at some time you have someone provide a similar data but the variables in different order. It might take way more time than it should to realize your output is different because of the missing columns statement.

Similarly, the Define statement to reinforce if a variable is display, group/order, analysis makes a difference and worth including.