topic Re: ods options for tab_color not working in SAS Programming

ods options for tab_color not working

shellp55 — Thu, 18 Apr 2019 18:54:54 GMT

Hello

I have SAS version 9.4. I am using the tagsets.ExcelXP to export data to an Excel workbook and want to change the tab color. According to items I've read, the option of tab_color('color') should work, allowing for the word of the color i.e. 'green' or the hexadecimal value. Below is my code but the tab_color function doesn't work (everything else does).

I found some code from the SAS website to edit the template and create tagsets.ExcelXP_mod which allows for tabcolor= but it seems to only allow names i.e. 'red', 'green' but not hexadecimal or other descriptions for the color. All assistance greatly appreciated.

options center;
ods listing close;
ods tagsets.ExcelXP path=&T_Path 
file=&F_Name style=printer;
title 'DAD_DQ'; 

/*Worksheet 2 - demographics*/
ods tagsets.ExcelXP options(sheet_name='Demo' Absolute_Column_Width='5,8,8,8,8,12,8,8,8,8'
		Autofit_height='yes' tab_color='green');
proc print data=demo_1 noobs style(Data)=[just=left] style(Header)=[just=center];
var inst;
var chartno;
var acctno;
var disdate;
var coder/style(data)={tagattr='format:@'};
var HCN/style(data)={tagattr='format:@'};
var Postal;
var Rescode/style(data)={tagattr='format:@'};
var ProvIss;
var RFP/ style(data)={tagattr='format:@'};
run; quit;

ods tagsets.ExcelXP close;

Re: ods options for tab_color not working

Reeza — Thu, 18 Apr 2019 18:57:05 GMT

How are you specifying the colours? Usually they're specified as CXFFFFFF with the CX in front. Not sure if you need to use SAS specifications here or excels though...

Do you have a link to the website where you got the code?

Re: ods options for tab_color not working

shellp55 — Thu, 18 Apr 2019 19:03:35 GMT

Hey Reeza, thanks for responding! The code was from http://support.sas.com/kb/45/addl/fusion_45249_1_tabcolor.sas.txt but I've also provided below:

proc template;
      define tagset tagsets.ExcelXP_Mod;
       parent=tagsets.excelxp;
  
       define event tabcolor;
         set $tabcolor $options['TABCOLOR'];

         set $tab_color["black" ] "0";
         set $tab_color["white" ] "1";
         set $tab_color["red" ] "2";
         set $tab_color["green" ] "3";
         set $tab_color["blue" ] "4";
         set $tab_color["yellow" ] "5";
         set $tab_color["magenta" ] "6";
         set $tab_color["cyan" ] "7";
         set $tab_color[]  lowcase($options['TABCOLOR']);

         do / if $tab_color[$tabcolor];
            put "<x:TabColorIndex>" $tab_color[$tabcolor];
         else;
            put "<x:TabColorIndex>" $tabcolor;
         done;
            put "</x:TabColorIndex>" NL /if $tabcolor;
         done;
      end;

       define event worksheet;
          start:

          do /if ^$doing_blank;

             trigger do_blank_worksheet /if $blank_sheet_name;
          done;

           trigger get_global_margins;

           do /if $proclist[proc_name];
              putlog "Excel XML does not support output from Proc:"
                      proc_name;
              putlog "Output will not be created.";
              break;
           done;

            break /if $worksheet_started;
            unset $cellwidths;
            unset $worksheet_widths;
            unset $worksheet_has_panes;
            unset $worksheet_has_autofilter;
            unset $worksheet_has_titles;
            unset $possible_row_repeat_start;
            unset $possible_row_repeat_end;
            unset $possible_col_repeat_start;
            unset $possible_col_repeat_end;

            do /if ^$worksheet_row;
                 eval $worksheet_row 0;
             done;

             eval $numberOfWorksheets $numberOfWorksheets +1;
             trigger worksheet_label;
             trigger clean_worksheet_label;
             trigger contents_entry /if ^cmp( event_name, "byline");

            do /if cmp( $worksheetname, " ");
                  eval $numberOfWorksheets $numberOfWorksheets -1;
             done;

             do /if $debug_level = -8;
                putlog "!!!!!!!!" Event_name "  Worksheet: |" $
                           worksheetname "|  " $numberOfWorksheets;
             done;
             unset $$worksheet_start;
             open worksheet_start;
              put "<x:WorksheetOptions xmlns=""urn:schemas-microsoft-com
:office:excel"">" NL;

             do /if $fittopage;
                     put "<FitToPage />" NL;
             done;
             put "<Print>" NL;
             put "<ValidPrinterInfo/>" NL;
             trigger do_paperSize;

             do /if $scale;
                 put "<Scale>";
                 put $scale;
                 put "</Scale>" NL;
             done;

                  put "<FitWidth>" $pages_fitwidth "</FitWidth>" NL;
                  put "<FitHeight>" $pages_fitheight "</FitHeight>" NL;
                  put "<LeftToRight/>" NL /if $left_to_right;
                  put "<HorizontalResolution>";
                  put $print_dpi;
                  put "</HorizontalResolution>" NL;
                  put "<VerticalResolution>";
                  put $print_dpi;
                  put "</VerticalResolution>" NL;
                  put "<Gridlines/>" NL /if $gridlines;
                  put "<BlackAndWhite/>" NL /if $blackandwhite;
                  put "<DraftQuality/>" NL /if $draftquality;
                  put "<RowColHeadings/>" NL /if $RowColHeadings;
                  put "</Print>" NL;
                  trigger tabcolor;
                  put "<Zoom>" $Zoom "</Zoom>" NL /if $Zoom;
                  put "<PageLayoutZoom>" $PageLayoutZoom "</PageLayoutZoom>"
                        NL /if $PageLayoutZoom;
                  put "<x:PageSetup>" NL;
                  put $$page_setup;

                  do /if ($embedded_titles &  ^ $system_title_setup) |  ^ $
                           system_title_setup;

                     do /if $print_header &  ^ $xheader;
                        putq "<x:header x:Data=" $print_header;
                        putq " x:Margin=" $print_header_margin;
                        put "/>" NL;
                        unset $xheader;
                     done;

                  done;

                  unset $system_title_setup;

                  do /if ($embedded_footnotes &  ^ $system_footer_setup) |  ^
                           $system_footer_setup;

                     do /if $print_footer &  ^ $xfooter;
                        putq "<x:Footer x:Data=" $print_footer;
                        putq " x:Margin=" $print_footer_margin;
                        put "/>" NL;
                        unset $xfooter;
                     done;

                  done;

                  unset $system_footer_setup;

                  do /if any( $landscape, $center_horizontal, $center_Vertical
                           );
                     put "<Layout";
                     put " x:Orientation=""Landscape""" /if $landscape;
                     put " x:CenterHorizontal=""1""" /if $center_horizontal;
                     put " x:CenterVertical=""1""" /if $center_vertical;
                     put "/>" NL;
                  done;


                  do /if any( $marginbottom, $marginleft, $marginright, $
                           margintop);
                     put "<PageMargins";
                     putq " x:Bottom=" $marginbottom;
                     putq " x:Left=" $marginleft;
                     putq " x:Right=" $marginright;
                     putq " x:Top=" $margintop;
                     put "/>" NL;
                  done;

                  put "</x:PageSetup>" NL;
                  close;

                  open worksheet;
                  set $worksheet_started "True";

               finish:
                  break /if $proclist[proc_name];
                  break /if ^$worksheet_started;
                  unset $worksheet_started;

                  do /if ^$$worksheet;

                     do /if ^$doing_blank;

                        do /if ^cmp( $worksheetname, " ");
                           eval $numberOfWorksheets $numberOfWorksheets -1;
                        done;

                        break;
                     done;

                  done;


                  trigger write_contents_entries;

                  trigger worksheet_tab;
                  unset $tabname_is_done;

                  open master_worksheet;
                  putq "<Worksheet ss:Name=" $worksheetName ">" NL;

                  trigger print_repeats;
                  set $current_worksheet $worksheetName;
                  unset $tempWorksheetName;
                  unset $worksheetName;
                  put $$worksheet_start;
                  unset $$worksheet_start;

                  trigger worksheet_head_end;

                  trigger table_start;
                  put $$worksheet;
                  putl "</Table>";
                  eval $table_count 0;
                  unset $$worksheet;
                  unset $byGroupLabel;
                  putl "</Worksheet>";
                  eval $worksheet_row 0;
              end;            
           end;   
      run;

Re: ods options for tab_color not working

Reeza — Thu, 18 Apr 2019 19:07:45 GMT

So, when that code was generated I'm pretty sure there was a smaller list to set the colours. In the newest version of Excel you can use any colour.

Try using the same specification as in ODS EXCEL? Can you switch to ODS Excel instead actually?

ods excel options (tab_color='red');
ods excel options (tab_color='#ff0000');
ods excel options (tab_color='rgba(0,100%,0,0.5)';

https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVersion=9.4&locale=en

Re: ods options for tab_color not working

shellp55 — Thu, 18 Apr 2019 19:13:08 GMT

I have 50 sheets written with tagsets.ExcelXP so it might be a bit of work but how different is the syntax for Excel different from the tagsets? For this particular project, tab color is not at all important and it is probably faster to do manually than to do via SAS but I've been bugged about this feature for awhile now and darn it I want tab colors to change!!

Thanks again Reeza!

Re: ods options for tab_color not working

Reeza — Thu, 18 Apr 2019 19:25:48 GMT

You'd have to verify the options list but I think everything you've shown is also in ODS EXCEL - and it creates native XLSX files directly, not an XML file. The specification style is pretty much the same so you'd be changing only ODS TAGSETS.EXCELXP line primarily. The remaining code would be the same. But....I totally get not wanting to refactor working code for such a small issue. My 'lazy approach' would be to write a small VBA macro or VBS to do it...assuming the specifications above don't work.

Re: ods options for tab_color not working

shellp55 — Thu, 18 Apr 2019 19:41:46 GMT

Thanks Reeza. Good advice. I'll try to rewrite one and if it is too labour intensive, I'll just move on.

Re: ods options for tab_color not working

Reeza — Thu, 18 Apr 2019 19:46:54 GMT

FYI - for the new tagset you linked to you, you need to use ODS TAGSETS.EXCELXP_mod

Your code as shown doesn't do that, what happens if you fix that? It references the parent tagsets so should still maintain all features.

I tried all the options above but couldn't figure it out 😞

I also looked at the XML file and it appears to be using #FFFFF format but at the same time couldn't find the blue (0000FF) specified :(.

My next debugging step would be to create two files, one with colours, one without and do a file comparison to see how it's being set and work backwards from there. Unfortunately I don't have the time to do that now.