Alternate column coloring using proc report

[hporter]

I have a dataset that I export to excel that looks like this:

YRMO	Var1	Var2
202112	50.2	61
202201	49.66	51.11

I have several tables I'm exporting to one excel file that all have different column names. 

I am trying to use proc report to alternate column shading for every other column. 

For example, the entire YRMO column would be shaded light gray, then Var1 would not be shaded, then Var2 would be shaded light gray.

I have done this with rows before, and was able to use the mod function. 

But now that I'm using character column names, I would like to avoid listing out all the different columns.

Is there any way to do the following without having to list out all the column names?

proc report data=work.have;

compute yrmo;
call define(_col_, "style", "style=[background=lightgray]");
endcomp;
compute var2;
call define(_col_, "style", "style=[background=lightgray]");
endcomp;
compute var4;
call define(_col_, "style", "style=[background=lightgray]");
endcomp;
compute var6;
call define(_col_, "style", "style=[background=lightgray]");
endcomp;
run;

[PaigeMiller]

I suppose you could write a macro that determines all the variable names, selects every 2nd column and produces the proper code inside of PROC REPORT to make only those columns light gray.

[andreas_lds]

Why proc report? If more happens, then colouring columns, please post full-code along with example data in usable form (no excel please), so that we can suggest working code.

The following example uses sashelp.class and proc print:

proc sql;
   create view work.meta as
      select name, varnum from sashelp.vcolumn
         where libname = 'SASHELP' and memname = 'CLASS'
         order by varnum
   ;
quit;


ods excel file="FIX_PATH\colours.xlsx";
data _null_;
   set work.meta end=jobDone;

   length statement $ 100;

   if _n_ = 1 then do;
      call execute('proc print data=sashelp.class noobs;');
   end;

   statement = catx(' ', 'var', name);

   if mod(_n_, 2) = 1 then do;
      statement = catx(' ', statement, '/ style={backgroundcolor=lightgrey};');
   end;
   else do;
      statement = cats(statement, ';');
   end;

   call execute(statement);

   if jobDone then do;
      call execute('run;');
   end;

run;
ods excel close;

[BrunoMueller · Accepted solution]

The sample code below colors alternate columns with a different background. Just the data part has a different background, the column header is still the default. If the column header should have the same background I would go with the sample from @andreas_lds and add an additional style definition for the header.

%let dsn = sashelp.cars;

/*
 * get number of cols
 */
data _NULL_;
  dsid = open("&dsn");                 /* open data set attributes */
  vars = attrn(dsid,"NVARS");            /* get number of variables */
  call symputx('nVars',vars);
  dsid = close(dsid);                      /* close data set attributes */
run;

proc report data=&dsn;
  column _all_ _dummy;
  define _dummy / noprint;

  compute _dummy;
    do col = 2 to &nVars by 2;
      call define(col, "style", "style=[background=lightgray]");
    end;
  endcomp;
run;

[hporter]

This is exactly what I wanted. Thank you very much.

[hporter]

Hey actually I have a follow-up question. So I ran your code with sashelp.cars and the results were exactly what I wanted. 

I applied this to my own code and it did accomplish the alternate column coloring, but it summed up all the data in each row so that my 12 row table turned into a 1 row table.

Just to prove I used your code correctly, I did this (only changed the sas table, which was my own table)

%let dsn = work.repo_final;

data _NULL_;
dsid = open("&dsn");
vars = attrn(dsid,"NVARS");
call symputx('nVars',vars);
dsid = close(dsid);
run;

proc report data=&dsn;
column _all_ _dummy;
define _dummy / noprint;

compute _dummy;
do col = 2 to &nVars by 2;
call define(col, "style", "style=[background=lightgray]");
end;
endcomp;
run;

I was able to come up with a work-around, but I am beyond confused as to why it worked. All of my columns in my table were numeric, so YRMO was the first column and was a numeric year-month combo. Example: 202001, 202002, 202003, 202004.

I convered YRMO to a string, and then re-ran your code and all of a sudden it stopped summing up the each column and instead gave me the result I wanted which was just to shade the alternate columns and not alter the data values at all.

Is there a reason as to why my converting the first field to a string made any difference??