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;
I found the solution.
Posted in the question.
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;
So you put the macro inside Proc Report.
I put it outside.
Nice to know.
Thanks.
HHC
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.