Call Define Last Column in Proc Report?

[grodman]

I have a proc report in which I am using an across variable, and want to dynamically format cells based on the value in an adjacent cell.  I know that when using "across" variables, you have to point directly to the cell (using "_C5_" as opposed to "columnname"), however, when I point to the last column of the proc report, the call define doesn't initiate. 

For example, if you run the program below, you will create a proc report that looks like the attachment.  The compute statement logic properly identifies _C5_ as "2" and executes a call define on cell _C4_, as it should.  However, the compute statement & logic either doesn't identify cell _C7_ as 2, or it's not executing the call define on cell _c6_.

Does anyone know why this is?  Or how this can be fixed?

Thanks in advanced for your help!!

Georgeproc

data work.data;

   infile datalines dsd;

   informat group $5. value 5. value2 $5. value3 5.;

   input group value value2 value3;

   datalines;

A,1,Cat,1

A,2,Dog,2

B,3,Cat,1

B,4,Dog,2

;;;;

run;

proc report data=work.data out=work.test;

    columns group value,(value2 value3);

    define group / group;

    define value / across;

define value2 / display;

compute value2;

  if _C3_ = 2 then do; call define("_C2_","style","Style=[Background=#C7E9FF]"); end;

  if _C5_ = 2 then do; call define("_C4_","style","Style=[Background=#C7E9FF]"); end;

  if _C7_ = 2 then do; call define("_C6_","style","Style=[Background=#C7E9FF]"); end;

endcomp;

run;

[Cynthia_sas]

Hi, maybe I'm missing something, but your across variable has 4 unique values 1, 2, 3, and 4. That means your "pairs" are

for 1 _C2_, _C3_

for 2 _C4_, _C5_

for 3 _C6_, _C7_

for 4 _C8_, _C9_

  In your compute block, you do not have an explicit test for _C9_, which would in turn highlight _C8_. PROC REPORT can ONLY test what you have coded in the COMPUTE block. If you have more values or columns than you test, PROC REPORT won't suddenly say to itself, "hey, he should be testing _C9_, too." If you don't code it, PROC REPORT doesn't do it.

  This is one of the reasons that people frequently use a SAS Macro program to figure out the number of unique values for the ACROSS variable and then automatically write the code for the IF statements.

  I hope this points you in the right direction...

cynthia

[grodman]

Hi Cynthia - that was an initial error on my part... but even when I add the compute statement for C9 and C8, it still doesn't work.  I've updated the proc report in the example below, and it still doesn't highlight C8 even though C9 meets the compute statment logic. Can you please run this and let me know what I'm doing wrong?

Thanks!

data work.data;

   infile datalines dsd;

   informat group $5. value 5. value2 $5. value3 5.;

   input group value value2 value3;

   datalines;

A,1,Cat,1

A,2,Dog,2

B,3,Cat,1

B,4,Dog,2

;;;;

run;

proc report data=work.data out=work.test;

    columns group value,(value2 value3);

    define group / group;

    define value / across;

define value2 / display;

compute value2;

  if _C3_ = 2 then do; call define("_C2_","style","Style=[Background=#C7E9FF]"); end;

  if _C5_ = 2 then do; call define("_C4_","style","Style=[Background=#C7E9FF]"); end;

  if _C7_ = 2 then do; call define("_C6_","style","Style=[Background=#C7E9FF]"); end;

  if _C9_ = 2 then do; call define("_C8_","style","Style=[Background=#C7E9FF]"); end;

endcomp;

run;

[Cynthia_sas]

Hi,

  Sorry, I'm teaching and not in a position to test code. I have a few quick comments before I have to get back.

1) I don't see a DEFINE for VALUE3, Generally, I would expect to see a DEFINE for both VALUE2 and VALUE3 with the VALUE3 item having a usage of ANALYSIS or of SUM. It's a small thing, I know; and probably would not make any difference, but the first thing I do when I look at a program is figure out how to read it and I need to see the DEFINE for every item in order to really understand everything that's going on. Minor point.

2) And, if I was coding this, I would code the COMPUTE block for VALUE3, (not VALUE2), because the IF condition is for the VALUE3 column. And yes, you are highlighting the VALUE2 column, but the IF test if for the VALUE3 column. Inside the compute block for VALUE3, I know that I can highlight any item that appears -before- VALUE3 item in the COLUMN statement (which would be the VALUE2 columns or the GROUP column). I didn't notice before that you used a COMPUTE block for VALUE2 instead of VALUE3. I'm not sure why that would make a difference, but it might be worth trying to just change your COMPUTE VALUE2 to COMPUTE VALUE3 -- Again, I'm not sure why I would code it that way, but instinctively, when I saw the COMPUTE VALUE2, I wanted it to be VALUE3.

  But the bottom line is I would recommend that you to open a track with Tech Support on this.

cynthia

[Orsini · Accepted solution]

I made the changes that Cynthia described and it seems to work OK. 

proc report data=work.data out=work.test;
    columns group value,(value2 value3);
    define group / group;
    define value / across;
    define value2 / display;
    define value3 / analysis;

    compute value3;
      if _C3_ = 2 then do; call define("_C2_","style","Style=[Background=#C7E9FF]"); end;
      if _C5_ = 2 then do; call define("_C4_","style","Style=[Background=#C7E9FF]"); end;
      if _C7_ = 2 then do; call define("_C6_","style","Style=[Background=#C7E9FF]"); end;
     if _C9_ = 2 then do; call define("_C8_","style","Style=[Background=#C7E9FF]"); end;
    endcomp;
run;

[grodman]

This worked!!  Thanks so much!!!