computing arrays in compute blocks in proc report?

[LB]

Happy Friday all! 

Not sure how to get started on this one- 

So I have a proc report with essentially a facility name and then col1-colx;

Generally columns col1-col4 are always populated with data so I am only concerned with col5-colx.

Colx is dynamic and may change weekly from 10-20 depending.

So in a compute block I want to do: 

compute ? 

if col[i]=' ' then call define(_COL_,'style','style={background=white color=white}');
if col[i]^=' ' then call define(_COL_,'style','style={background=red color=black}');

endcomp;

so that all the col5-colx will be blank if null and red if there is a value. 

I can't span the data per se given the way the data is structured without rewriting large parts of it-otherwise I would do that. 

 I can write a macro to contend with this per se but seeing if there is a simpler solution. 

TIA- 

Lawrence

[Cynthia_sas]

Hi:
You would either need a separate compute block for each col value or you would need to have a dummy variable at the end of the report row, that you let you address each of the previous columns on the row.

Critical to coding this will be understanding whether you list col1-colx on the column statement or whether you have them under an ACROSS item, that will make a difference in how you declare the array members.

cynthia

[LB]

Hi Cynthia- 

They are listed as col1-colx on the column statement.  I'm trying to avoid computing each column5-colx  as colx is variable week to week- 

I am trying to make use of this  link-

http://support.sas.com/kb/43/765.html 

but still having some challenges  

&mstoolo- is equal to col1-colx

Below I am just trying to do it for for 4 columns-Once I figure it out I can insert macro vars. 

proc report data=stoolie_tg;
columns facid ("Numbers of stools*^" &mstoolo) dummy;
....
define dummy / computed noprint;
compute dummy;

array flag(4) stool5 stool6 stool7 stool8;
do i=1 to dim(flag);
if flag(i)=' ' then call define(_COL_,'style','style={background=white color=white}');
if flag(i)^=' ' then call define(_COL_,'style','style={background=salmon color=white}');
end;
endcomp;
run;

Thanks again. 

Lawrence

[Cynthia_sas]

Hi:

  If you're just listing them in the COLUMN statement, I'd be tempted to use PROC FORMAT. Something like this -- since you didn't supply data, I made fake data with SASHELP.CLASS -- only 4 extra vars:

data testit;
  set sashelp.class;
  col1 = age+height;
  col2 = age+height+weight;
  col3 = height+weight;
  col4 = age*age;
  if sex = 'M' and age = 13 then col3 = .;
  else if sex = 'F' and age = 14 then col4 = .;
run;
  
proc format;
  value bck_val .='white'
                other='lightred';
  value for_val .=white
                other='black';
run;
  
proc report data=testit;
  column name age sex height weight col1 col2 col3 col4 ;
  define name / order;
  define age / display;
  define sex / display;
  define height / display;
  define weight / display;
  define col1 / display; 
  define col2 / display; 
  define col3 / display style(column)={background=bck_val. color=for_val.}; 
  define col4 / display style(column)={background=bck_val. color=for_val.};
 run;
 

  

and that code produces this:

with only col3 and col4 highlighted based on the user-defined format.

cynthia

[LB · Accepted solution]

Cynthia- 

-the only issue with the solution is contending with the dynamic nature of how many columns per week- 

As I am about as stubborn as my dog (beagle) I managed to get the array to work. I just took that link and adapted accordingly- 

&arrya & arryb are subsets of &mstoolo  

proc report data=stoolie_tg;
columns facid ("Numbers of stools*^" &mstoolo ) dummy;
define stool0/style (column)={background=salmon just=center color=black};
define stool1/style (column)={background=orange just=center color=black};
define stool2/style (column)={background=#c9df8a just=center color=black};
define stool3/style (column)={background=#c9df8a just=center color=black};
define dummy /noprint;
compute stool4;
if stool4=' ' then call define(_COL_,'style','style={background=white color=white}');
if stool4^=' ' then call define(_COL_,'style','style={background=orange color=black}');
endcomp;

compute dummy;
array name(12) $ (&arrya);
array flag(12) &arryb;
do i=1 to dim(name);
call define(name(i),'style','style=[background='||put(flag(i),$bck_val.) ||']');
end;
endcomp;
run;

[Cynthia_sas]

Ah, well, my tendency would have been to stick with the format and use a macro program to dynamically generate the column statement and the DEFINE statements with the format where I needed it.

But I am glad you made the array work.

cynthia

[LB]

That was my next  step if I couldn't.  

Lawrence