BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LB
Quartz | Level 8 LB
Quartz | Level 8

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LB
Quartz | Level 8 LB
Quartz | Level 8

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;

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ
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
Quartz | Level 8 LB
Quartz | Level 8

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
SAS Super FREQ

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:

use_format_report.png

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

 

cynthia

 

LB
Quartz | Level 8 LB
Quartz | Level 8

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
SAS Super FREQ
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
Quartz | Level 8 LB
Quartz | Level 8

That was my next  step if I couldn't.  

Lawrence

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1333 views
  • 0 likes
  • 2 in conversation