PROC REPORT apply color of format to a different column

[PaigeMiller]

First, I create a table in PROC REPORT where cells are colored according to a predefined format.

proc format;
    value msrp 0-20000=yellow 20000<-40000=lightmoderategreen 40000<-99999=lightmoderatered;
run;
ods excel file='temp.xlsx';
proc report data=sashelp.cars(obs=200);
    columns type origin,msrp dummy;
    define type/group;
    define origin/across;
    define msrp/mean;
    define dummy/noprint;
    compute dummy;
        call define("_c2_","style","style={background=msrp.}");
        call define("_c3_","style","style={background=msrp.}");
        call define("_c4_","style","style={background=msrp.}");
    endcompute;
run;
ods excel close;

Looks good!

Now, I want a different table, where instead of MSRP, I want horsepower but I want the colors to remain the same, based upon the format applied to MSRP. It looks like this (fake numbers):

See, same colors based upon the format applied to MSRP, but the cells contain the horsepower numbers.

I can get the table without the colors, but I don't know what to put in the compute block; as shown the compute block doesn't do what I want. How can I get this second table with the HP numbers but the colors based upon the MSRP colors?

ods excel file='temp.xlsx';
proc report data=sashelp.cars(obs=200) out=_ABCD_;
    columns type origin,(msrp horsepower) dummy;
    define type/group;
    define origin/across;
    define msrp/mean noprint;
    define horsepower/'HP' mean;
    define dummy/noprint;
    compute dummy;
        call define("_c5_","style","style={background=msrp.}");
        call define("_c6_","style","style={background=msrp.}");
        call define("_c7_","style","style={background=msrp.}");
    endcompute;
run;
ods excel close;

Or is it not possible?

[Tom]

Do the column number really change if you add a NOPRINT column?

[ballardw]

I think that first the column numbers need to be addressed. Consider this:

proc report data=sashelp.cars(obs=200) out=_ABCD_;
    columns type origin,(msrp horsepower) dummy;
    define type/group;
    define origin/across;
    define msrp/mean noprint;
    define horsepower/'HP' mean;
    define dummy/noprint;
    compute dummy;
        call define("_c3_","style","style={background=yellow}");
        call define("_c5_","style","style={background=green}");
        call define("_c7_","style","style={background=blue}");
    endcompute;
run;

If the columns used are _c5_  _c6_  and _c7_ the Europe column is yellow and the USA is blue and the Asia is not colored. So getting the columns numbered correctly appears to be the first challenge.

And this duplicates the coloring but not using the format. Perhaps a clue though:

proc report data=sashelp.cars(obs=200) out=_ABCD_;
    columns type origin,(msrp horsepower) dummy;
    define type/group;
    define origin/across;
    define msrp/mean noprint;
    define horsepower/'HP' mean;
    define dummy/computed noprint;
    compute dummy;
        if 0 le _c2_ le 20000 then 
        call define("_c3_","style","style={background=yellow}");
        if 20000 lt _c2_ le 40000 then 
        call define("_c3_","style","style={background=lightmoderategreen}");
        if 40000 lt _c2_ le 999999 then 
        call define("_c3_","style","style={background=lightmoderatered}");

        if 0 le _c4_ le 20000 then 
        call define("_c5_","style","style={background=yellow}");
        if 20000 lt _c4_ le 40000 then 
        call define("_c5_","style","style={background=lightmoderategreen}");
        if 40000 lt _c4_ le 999999 then 
        call define("_c5_","style","style={background=lightmoderatered}");


        if 0 le _c6_ le 20000 then 
        call define("_c7_","style","style={background=yellow}");
        if 20000 lt _c6_ le 40000 then 
        call define("_c7_","style","style={background=lightmoderategreen}");
        if 40000 lt _c6_ le 999999 then 
        call define("_c7_","style","style={background=lightmoderatered}");
    endcomp;
run;

[PaigeMiller]

@ballardw Thanks, I will mark your answer correct in a day or two, I'm hoping someone else has a better idea. As you might imagine, the real the problem is not about SASHELP.CARS — the real problem has 21 columns going across, and the format has 10–12 different levels. Of course, I can write a macro to implement your suggestion if no one else has a better idea.

[Cynthia_sas · Accepted solution]

Hi,

Yes, what you want to do IS possible. It is just sort of fiddly. Look at output 10 in this paper https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf. Specifically, look at the adjusted code where I create a variable called "SVARn" and then use that variable in the Call Define.

Cynthia

[PaigeMiller]

@Cynthia_sas 

This looks like a solution I can use. On page 11 it says

To see the full code for the COMPUTE block, download the ZIP file of programs that will out on the R&D website on support.sas.com. As a bonus, there is a “macroized” version of this program in the ZIP file, too

but I don't know what the link is to find this code.

[Cynthia_sas]

Hi...sorry, it was moved. YOu'll find it here: https://support.sas.com/resources/papers/proceedings14/index.html scroll down to the S in the alpha list and there's a link to Download the zip file:

Have fun with the code!

Cynthia