Help using Base SAS procedures

How to color the row automatically?

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

How to color the row automatically?

 

Hello,

 

I want to color a table that is rolled into a new row automatically monthly, (see the code below). for example, when in January, I have two rows, month1, and total, then I want to color only one row that 'total' is there as grey; when in Feburary, I have three rows, month1, month2, and total, then I want to color only one row that 'month2' is there as grey;when in March, I have four rows, month1, month2, month3, and total, then I want to color two rows that 'month2' and 'total' is there as grey, and so on and on...would you please correct the following code, to make it? Thank you so much!


data new;
input month $ mid resp exp rev;
datalines;
1 22 0.45 0.45 0.35
2 33 0.35 0.45 0.35
3 44 0.65 0.65 0.55
4 55 0.55 0.65 0.55
5 66 0.42 0.42 0.65
6 77 0.65 0.42 0.65
total 297 0.48 0.67 0.56
;
run;

 

Proc report data=new contents=" " nowd split='\'
style(header)={ color=white cellwidth=1.7in background=cornflowerblue height=1.7in fontsize=1.5 }

/*style(column)={just=center background=snow foreground=black fontsize=1.5 }; */
style(column)={just=center background=snow cellwidth=1.6in foreground=black fontsize=1.5 };


column month mid resp exp rev;
footnote j=l" ";

define month/ "Date" style(column)={cellwidth=1in };
define mid / "Numbers" style(column)={cellwidth=1in};
define resp / " Approvals" style(column)={cellwidth=1in};
define exp/ "  Rate" style(column)={cellwidth=1in};
define rev/ "Referral " style(column)={cellwidth=1in };


compute month ;

if month in ( 'total')
then do;
call define(_row_,'style','style={background=grey}');
end;

else if month in ( 2)
then do;
call define(_row_,'style','style={background=grey}');
end;

else if month in ( 2,'total')
then do;
call define(_row_,'style','style={background=grey}');
end;
else if month in ( 2,4)
then do;
call define(_row_,'style','style={background=grey}');
end;
else if month in ( 2,4,'total')
then do;
call define(_row_,'style','style={background=grey}');
end;

else if month in ( 2,4,6)
then do;
call define(_row_,'style','style={background=grey}');
end;

endcomp;

 

run;


Accepted Solutions
Solution
‎09-06-2016 11:26 AM
Super Contributor
Posts: 394

Re: How to color the row automatically?

[ Edited ]

Here's an example of how to create a report with alternating colors. This example creates Excel output. If you want a different kind of output (HTML or PDF, for example) just change the destination name from tagsets.excelxp to whatever you want.

/*
 *  Assign alternating colors to the rows of a report
 */
ods tagsets.excelxp file="altcolor.xml";
ods listing close;

proc report nowd data=sashelp.class
        style(header)=[background=#f0f0f0 foreground=black vjust=bottom];
    col name sex age height weight;
    define name--weight / display;
compute name;
    bg + 1;
    if mod(bg, 2) = 1 then
        call define(_row_, "style", "style={background=white}");
    else
        call define(_row_, "style", "style={background=#d1e9d1}");
endcomp;
run;

ods listing;
ods tagsets.excelxp close;

 

View solution in original post


All Replies
Super User
Posts: 19,869

Re: How to color the row automatically?

Your conditions don't make sense. 

 

Once one is evaluated as true the logic stops. But you have if month=2 in several different conditions?

 

Are you trying to do alternate row colours? 

If so, create a row counter and use the MOD function instead. Search alternate row colour to see examples on here. 

 

Contributor
Posts: 74

Re: How to color the row automatically?

Hi Reeza,

 

Unfortuantely, I have never tried MOD function, would you please give me an example? Yes, that is true, I really want to do alternate row colors, the first row is left colorless, then second row is grey, and then third row is colorless, and then fourth row is grey, and so on and on and the new month is rolling into the table, monthly, thank you so much!

Solution
‎09-06-2016 11:26 AM
Super Contributor
Posts: 394

Re: How to color the row automatically?

[ Edited ]

Here's an example of how to create a report with alternating colors. This example creates Excel output. If you want a different kind of output (HTML or PDF, for example) just change the destination name from tagsets.excelxp to whatever you want.

/*
 *  Assign alternating colors to the rows of a report
 */
ods tagsets.excelxp file="altcolor.xml";
ods listing close;

proc report nowd data=sashelp.class
        style(header)=[background=#f0f0f0 foreground=black vjust=bottom];
    col name sex age height weight;
    define name--weight / display;
compute name;
    bg + 1;
    if mod(bg, 2) = 1 then
        call define(_row_, "style", "style={background=white}");
    else
        call define(_row_, "style", "style={background=#d1e9d1}");
endcomp;
run;

ods listing;
ods tagsets.excelxp close;

 

Contributor
Posts: 74

Re: How to color the row automatically?

Tim,
I really appreciate your help! Thank you so much! Wish you all the best!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 369 views
  • 1 like
  • 3 in conversation