BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hporter
Obsidian | Level 7

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

YRMOVar1Var2
20211250.261
20220149.6651.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;



 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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;

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
andreas_lds
Jade | Level 19

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
SAS Super FREQ

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
Obsidian | Level 7

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

hporter
Obsidian | Level 7

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??

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 789 views
  • 3 likes
  • 4 in conversation