topic Re: Color cell with DO LOOP in SAS Programming

Color cell with DO LOOP

hhchenfx — Tue, 26 Mar 2019 04:30:37 GMT

Hi Everyone,

I have 2 set of column: s1 s2 and v1 v2.

I have 2 column color_cell1 and color_cell2.

If color_cell1>0 then color S1 and V1

If color_cell2>0 then color S2 and V2

So I can do it with macro variable created separately as you can see in my main code.

I need a DO LOOP since my actual data has a lot of column.

My Macro works!

In the original separated one, each location check create 1 set of color

In the Marco, it is the same set of color.

Thus the color pattern not match

(difficult to explain)

Thanks a lot.

HHCFX

*IT WORKS!*

%macro xxx();

proc report data=have nowd;
column id v1 v2 s1 s2 color_cell_1 color_cell_2;

define s1/display;
define s2/display;

define v1/display;
define v2/display;
define color_cell_1/display;
define color_cell_2/display;

	%DO location=1 %to 2;
		compute  color_cell_&location;
			if color_cell_&location>0 then do;
				do i=&location+1;
				call define (i,"style","style={background=RED}");
				end;

				do j=&location+3;
				call define (j,"style","style={background=BIYG}");
				end;

			end;
		endcomp;
	%END;

	run;
%mend;

%xxx();run;

****MAIN CODE**************;
data have;
input ID s1 s2 v1 v2 color_cell_1 color_cell_2;
datalines;
1 1 1 1 1 0 1
2 1 2 3 0 0 0
3 1 2 3 1 1 0
4 9 8 7 4 1 1
;run;



*MACRO VARIABLE to apply to the 2 sets;
%let location1=1;
%let location2=2;


proc report data=have nowd;
column id v1 v2 s1 s2 color_cell_1 color_cell_2;

define s1/display;
define s2/display;

define v1/display;
define v2/display;
define color_cell_1/display;
define color_cell_2/display;


*for set v1 s1 based on color_cell1;
compute  color_cell_&location1;
	if color_cell_&location1>0 then do;
		do i=&location1+1;
		call define (i,"style","style={background=RED}");
		end;

		do j=&location1+3;
		call define (j,"style","style={background=RED}");
		end;
	end;
endcomp;

*for set v2 s2 based on color_cell2;
compute  color_cell_&location2;
	if color_cell_&location2>0 then do;
		do i=&location2+1;
		call define (i,"style","style={background=BIYG}");
		end;

		do j=&location2+3;
		call define (j,"style","style={background=BIYG}");
		end;
	end;
endcomp;

run;

Re: Color cell with DO LOOP

hhchenfx — Tue, 26 Mar 2019 04:31:02 GMT

I found the solution.

Posted in the question.

Re: Color cell with DO LOOP

ChrisNZ — Tue, 26 Mar 2019 04:58:23 GMT

I don't see a solution anywhere, but this works:

****MAIN CODE**************;
data HAVE;
input ID V1 V2 S1 S2 COLOR_CELL_1 COLOR_CELL_2;
datalines;
1 1 1 1 1 0 1
2 1 2 3 0 0 0
3 1 2 3 1 1 0
4 9 8 7 4 1 1
run;

proc report data=HAVE nowd;
  column ID V1 V2 S1 S2 COLOR_CELL_1 COLOR_CELL_2;

  define S1/display;
  define S2/display;

  define V1/display;
  define V2/display;
  define COLOR_CELL_1/display;                 
  define COLOR_CELL_2/display;             

%macro loop;
  %local i;
  %do i=1 %to 2; 
  compute COLOR_CELL_&i;              
    if COLOR_CELL_&i.>0 then call define ("V&i","style","style={background=RED}");
    if COLOR_CELL_&i.>0 then call define ("S&i","style","style={background=RED}");
  endcomp;
  %end;
%mend; 
%loop

run;

Re: Color cell with DO LOOP

hhchenfx — Wed, 27 Mar 2019 01:33:59 GMT

So you put the macro inside Proc Report.

I put it outside.

Nice to know.

Thanks.

HHC

Re: Color cell with DO LOOP

ChrisNZ — Wed, 27 Mar 2019 02:59:48 GMT

Oh I see what you are doing now. So convoluted.

You can write the 25 lines of code as:

	%do location=1 %to 2;
		compute COLOR_CELL_&location.;
			if COLOR_CELL_&location. > 0 then do;
				call define (&location.+1,"style","style={background=RED }");
				call define (&location.+3,"style","style={background=BIYG}");
			end;
		endcomp;
	%end