Using Macro variable in proc format?

[SASAlex101]

I have a macro below that can be run in 1 program. It produces 2 variables for use for the user &computeblockprocreport. and &formatlines.

FormatLines are intended to be used in a proc format statement but it's not working as demo'd below. The computeblockprocreport is used in Proc report and is used to apply a gradient to a table if required in the compute block. THIS IS WORKING. 

%macro generate_gradient_colors(start_value,end_value, start_color, end_color, num_intervals,rpt_col_num); 
    /* Extract RGB components of start and end colors */
    %let start_red = %substr(&start_color, 3, 2);
    %let start_green = %substr(&start_color, 5, 2);
    %let start_blue = %substr(&start_color, 7, 2);
    
    %let end_red = %substr(&end_color, 3, 2);
    %let end_green = %substr(&end_color, 5, 2);
    %let end_blue = %substr(&end_color, 7, 2);

    %put &end_red;
	%put &end_green;
	%put &end_blue;
	
	%let start_red_decimal = %sysfunc(inputn(&start_red.,hex8.));
	%let start_green_decimal = %sysfunc(inputn(&start_green.,hex8.));
	%let start_blue_decimal = %sysfunc(inputn(&start_blue.,hex8.));

	%let end_red_decimal = %sysfunc(inputn(&end_red.,hex8.));
	%let end_green_decimal = %sysfunc(inputn(&end_green.,hex8.));
	%let end_blue_decimal = %sysfunc(inputn(&end_blue.,hex8.));

    /* Calculate the increment for each color component */
    %let red_increment = (%sysevalf(&end_red_decimal - &start_red_decimal)) / &num_intervals;
    %let green_increment = (%sysevalf(&end_green_decimal - &start_green_decimal)) / &num_intervals;
    %let blue_increment = (%sysevalf(&end_blue_decimal - &start_blue_decimal)) / &num_intervals;
  
    %let value_increment = %sysevalf((%sysevalf(&end_value - &start_value + 1) / &num_intervals));
	%let prev_increment = &start_value;
 
    /* Generate format lines with gradient colors */
    %GLOBAL formatlines;
    %GLOBAL computeblockprocreport;
    %do i = 1 %to &num_intervals;

        %let red_value = %sysevalf(&start_red_decimal + &red_increment * &i);
        %let green_value = %sysevalf(&start_green_decimal + &green_increment * &i);
        %let blue_value = %sysevalf(&start_blue_decimal + &blue_increment * &i);
        
        %let hex_color = cx%sysfunc(strip(%sysfunc(putn(&red_value, hex2.))))%sysfunc(strip(%sysfunc(putn(&green_value, hex2.))))%sysfunc(strip(%sysfunc(putn(&blue_value, hex2.))));
       %if &i. eq 1 %then %do;  
/*for Proc Format*/%let formatlines = low - &start_value. = %str(%')&hex_color.%str(%'); 
/*for Proc Report*/%let computeblockprocreport = if _C&rpt_col_num._ le &start_value. then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;);
		%end;
	   %if &i. lt &num_intervals %then %do;  
/*for Proc Format*/%let formatlines = &formatlines. &prev_increment - %sysevalf(&i. * &value_increment) = %str(%')&hex_color.%str(%');
/*for Proc Report*/%let computeblockprocreport = &computeblockprocreport. if _C&rpt_col_num._ gt &prev_increment and _C&rpt_col_num._ le %sysevalf(&i. * &value_increment) then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;);
		%end;
	   %if &i. eq &num_intervals %then %do;  
/*for Proc Format*/%let formatlines = &formatlines. &prev_increment - high = %str(%')&hex_color.%str(%');
/*for Proc Report*/%let computeblockprocreport = &computeblockprocreport. if _C&rpt_col_num._ gt &prev_increment then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;);
		%end;

		%let prev_increment = %sysevalf(&i. * &value_increment);
    %end;

%mend;


/* Example usage */
%let start_value = 1;
%let end_value = 10;
%let start_color = cxFFFFFF;
%let end_color = cx00A5B9;  
%let num_intervals = 5;
%let rpt_col_num = 1;

/* Generate gradient colors */
%generate_gradient_colors(&start_value, &end_value, &start_color, &end_color, &num_intervals,&rpt_col_num);

%put &formatlines.;
%put &computeblockprocreport.;


proc format; 
 value heatfmt 
&formatlines.
;
quit;

proc format; 
 value heatfmtmanual
low - 1 = 'cxCCEDF1' 
1 - 2 = 'cxCCEDF1' 
2 - 4 = 'cx99DBE3' 
4 - 6 = 'cx66C9D5' 
6 - 8 = 'cx33B7C7' 
8 - high = 'cx00A5B9'
;
quit;

data test;
do col1 = &start_value to &end_value;
rnum = STRIP(PUT(col1,best32.));
output;
end;
run;


/*TEST Proc Report COMPUTE BACKGROUND*/
Proc Report Data=work.test;
columns _ALL_;
define col1 / "col1";
compute col1; 
&computeblockprocreport.;
endcomp;
;
run;


/*TEST Proc TABULATE */
Proc Tabulate DAta=WORK.test
style=[background=heatfmt.];
;

class rnum;
var col1;
table rnum,col1
;
run;

/*TEST Proc TABULATE */
Proc Tabulate DAta=WORK.test
style=[background=heatfmtmanual.];
;

class rnum;
var col1;
table rnum,col1
;
run;

%put &computeblockprocreport.;
%put ;
%put &formatlines.;

The text in &formatlines. is:

low - 1 = 'cxCCEDF1'

1 - 2 = 'cxCCEDF1'

2 - 4 = 'cx99DBE3'

4 - 6 = 'cx66C9D5'

6 - 8 = 'cx33B7C7'

8 - high = 'cx00A5B9'

line 75 has the proc format and &formatlines variable that isn't working. 

line 81 has the manual version types out instead of using the &formatlines var.  

Line 100 and Line 111 are tests for Proc tabulate using the two formats. 

Only the manual format is working... 

Have I come too far just to realize this isn't possible to use macro vars in proc format?  

Thanks;

[data_null__]

Show the portion of the log with the ERROR.

Have you looked at the documentation for PROC FORMAT?  It is usually easier to create a CONTROL data set when generating a format from data.

[SASAlex101]

Funny, there is no error, the code works. there's a few notes about how the formats are already on the library WORK.FORMATS, and table observations. No errors.

[data_null__]

---

@SASAlex101 wrote:
Funny, there is no error, the code works. there's a few notes about how the formats are already on the library WORK.FORMATS, and table observations. No errors.

---

Well ERROR or not the answer is in the LOG.  Can you explain the problem again apparently, I don't get your problem.

[SASAlex101]

the problem seems to be when I'm using the formatlines variable in the proc format statement, it's not working. 

proc format;
value heatfmt
&formatlines.
;
quit;

[Quentin]

There is nothing special about referencing a macro variable in a PROC FORMAT step, so this:

proc format; 
 value heatfmt 
&formatlines.
;
quit;

Should be fine, assuming &FormatLines ressolves to a reasonable value.

If you run:

%put &=formatlines ;

proc format; 
 value heatfmt 
&formatlines.
;
quit;

What do you see for the resolved value of FORMATLINES? Is it what you expect?  Could there be hidden/unprintable characters?

[WarrenKuhfeld]

I'm not even going to begin to figure out what you are doing.
However, after writing tens of thousands of lines of macro code over the years, I can tell you my strategy. Never write macro code when you can avoid writing macro code. If you need to process a bunch of macro variables, do it in the context of DATA _NULL_, SYMGET, ordinary DATA step statements, and SYMPUT. Your life will be much easier.

[SASAlex101]

I'm making a macro that will create the code required to conditionally format stuff on a gradient.

[ballardw]

Some general suggestions:

First Proc Format will take a properly constructed data set with the CNTLIN= data set option. This is generally WAY easier than macro code. For one thing it is easy to look at the contents of the data set to verify it is correct before sending to Proc format.

Second, it is poor practice to define formats with the endpoints of a format appearing in two or more ranges.

I can't tell what your Proc Tabulate is supposed to test. BACKGROUNDCOLOR=format needs an appropriate value.  Your Tabulate code has a character variable and a numeric variable. The character variable can't use your format as it is numeric. So if that was intended you need a separate format and used on a CLASSLEV statement.

The body of the Proc tabulate would need the format attached to the statistic.

proc format; 
 value heatfmtmanual
low - 1 = 'cxCCEDF1' 
1<- 2 = 'cxCCEDF1' 
2<- 4 = 'cx99DBE3' 
4<- 6 = 'cx66C9D5' 
6<- 8 = 'cx33B7C7' 
8<- high = 'cx00A5B9'
;
run;
data work.test;
   do col1=1 to 12;
      rnum = STRIP(PUT(col1,best32.));
      output;
   end;
run;


Proc Tabulate DAta=WORK.test
;
   class rnum;
   var col1;
   table rnum,
         col1*sum=' '*[style= [backgroundcolor= heatfmtmanual.]]
;
run;

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

[SASAlex101]

the desired output in Proc tabulate is this: 

This was generated with heatfmtmanual. this way:

/*TEST Proc TABULATE */
Proc Tabulate DAta=WORK.test
style=[background=heatfmtmanual.];
;

class rnum;
var col1;
table rnum,col1
;
run;

now, sounds like using a control dataset is the way instead of macros... I have tried this: 

data heatfmt;
   input label $ start end background $ fmtname $;
   infile datalines delimiter="#" dsd;
   datalines;
-999#-999#0#cxCCEDF1#heatfmt
0#0#2#cxCCEDF1#heatfmt
2#2#4#cx99DBE3#heatfmt
4#4#6#cx66C9D5#heatfmt
6#6#8#cx33B7C7#heatfmt
8#8#10#cx00A5B9#heatfmt
;
run;

proc format library=work cntlin=heatfmt;
run;

/*TEST Proc TABULATE */
Proc Tabulate DAta=WORK.test
style= [backgroundcolor= heatfmt.]
;

class rnum;
var col1;
table rnum,col1
;
run;

the result is not colored... 

[ballardw · Accepted solution]

Run this code:

proc format library=work cntlout=work.cntlout; 
 value heatfmt 
&formatlines.
;
run;

Open Work.Cntlout and examine the values of the label.

I bet that you find the quote characters in the values shown which means that the string representing the color code is not cxCCEDF1 but 'cxCCEDF1' which is not a valid color code. This is a by product of using the macro language where everything is text. So when you used the %str(') to force the quotes into the macro you added characters you didn't want. SAS knows the rules for using the macro string. Drop the quotes from the Formatlines variable and try again.

Or better yet: USE a data step to build a cntlin data set. Like this:

data rgbtest ;
   length label $8.;
   fmtname = 'RGBColor';
   start=0;
   do r=0 to 100 by 10;
      do g=0 to 100 by 10;
         do b=0 to 100 by 10;
            start+1;
            end=start; /* this is where to add values for your end of interval*/
            rh= put(r,hex2.);
            gh= put(g,hex2.);
            bh= put(b,hex2.);
            label = cats("CX",rh,gh,bh);
            output;
          end;
       end;
    end;
    label rH='Red (HEX)'
          gh='Green (HEX)'
          bh='Blue (HEX)'
          ;

run;

proc format cntlin = rgbtest library=work;
run;

This would also let you add values to the optional variable HLO of L or H to indicate the given range includes LOW or HIGH; or to place Y in SEXCL (start exclude: the starting lower value is not used such as 2<- 4 in ) or EEXCL (end exclude or 2 -<4 in proc format range definition) N values indicate the end is included.

Which is a problem for your format as you have almost all the endpoints in two ranges. I don't know which value color you expect for those ends.

[SASAlex101]

Holy moly Batman! it worked. I removed the str(') from the proc report lines in the macro and it worked great.