topic Re: computing arrays in compute blocks in proc report? in SAS Procedures

computing arrays in compute blocks in proc report?

LB — Fri, 05 Jan 2018 17:58:54 GMT

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

Re: computing arrays in compute blocks in proc report?

Cynthia_sas — Fri, 05 Jan 2018 18:20:52 GMT

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

Re: computing arrays in compute blocks in proc report?

LB — Fri, 05 Jan 2018 18:29:31 GMT

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

Re: computing arrays in compute blocks in proc report?

Cynthia_sas — Fri, 05 Jan 2018 18:46:43 GMT

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

Re: computing arrays in compute blocks in proc report?

LB — Fri, 05 Jan 2018 20:22:57 GMT

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;

Re: computing arrays in compute blocks in proc report?

Cynthia_sas — Fri, 05 Jan 2018 20:29:55 GMT

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

Re: computing arrays in compute blocks in proc report?

LB — Fri, 05 Jan 2018 20:39:33 GMT

That was my next step if I couldn't.

Lawrence