How to change the header column and font color in ods

LMSSAS — Mon, 04 Apr 2022 19:36:42 GMT

Hello, I am using the following proc sql and code to create an ODS file and i want to give the column headers a specific background color and font color so it matches other reports. How would I solver for this, the other reports are using the following:

style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,79,138) background=%RGB(0,79,138) foreground=whitesmoke font_weight=bold];

proc sql;
	create table AHIPNeeded2 as
		select distinct
			* from AHIPNeeded 
			where 'toh_agency'n = "&rep.";

proc sql;
	create table READYTOTRAIN2 as
		select distinct
			* from READYTOTRAIN
			where 'toh_agency'n = "&rep.";

proc sql;
	create table ReadyToSell2 as
		select distinct
			* from READYTOSELL
			where 'toh_agency'n = "&rep.";


ods excel options(sheet_name="AHIP Needed" autofilter='all');
proc report data=AHIPNeeded2;

ods excel options(sheet_name="Ready To Train" autofilter='all');
proc report data=READYTOTRAIN2;
run;

ods excel options(sheet_name="Ready To Sell" autofilter='all');
proc report data=READYTOSELL2;
run;

Re: How to change the header column and font color in ods

ballardw — Mon, 04 Apr 2022 20:01:32 GMT

Since you say "other reports" have that line of code, have you tried placing that code in the same position of your Proc Report?

The main likelihood for a problem is the presence of a macro variable for the font in this snippet: "[font=("&fonttype.",11pt) " . You would have to make sure your program has the same value of the macro variable Fonttype assigned.

Re: How to change the header column and font color in ods

LMSSAS — Mon, 04 Apr 2022 20:41:52 GMT

@ballardw can you look at my code and tell me if it looks correct? I included the original report that uses the macro and then my additional report. I just want to make sure I am assigning the macro correctly.

proc sql;
	create table TOH_Data as
		select distinct 
			a.*
			from medicaresalesagencies3 a
					where toh_agency is not null 
					and toh_agency not in ('K022');
					quit;
/*Code will drop the table on SO_SHARE*/
/*proc sql;
	create table SO_SHARE.TOH_Data as
		select distinct 
			a.*
			from medicaresalesagencies3 a
				where toh_agency is not null 
					and toh_agency not in ('K022');
					quit;*/

/* create a list of the broker manager's name to use in the loop macro to create sub reports */
proc sql;
	select distinct
		compress(toh_agency,"-") into : toh_agency1 separated " "
	from TOH_Data
	where toh_agency like ('S%') or toh_agency like ('K%') and toh_agency is not missing
	;
quit;

/* This is to pull in the FMO status report information to create the pre-status appointments */
proc sql;
	create table summary_outputII as
		select distinct
		a.*,
		b.'LEVEL 2'n as 'Level_Down 2'n,
		b.'LEVEL 3'n as 'Level_Down 3'n,
		b.'LEVEL 4'n as 'Level_Down 4'n,
		case
		when b.TOH_Agency is null then aor
		else b.TOH_Agency end as 'TOH_Agency'n
		from summary_output a
			left join Hierarchy_final b
				on a.aor = b.'level 4'n;
				quit;


/*proc sql;*/
/*	select distinct*/
/*		compress(toh_agency," -") into : toh_agency2 separated " "*/
/*	from summary_outputII*/
/*	where toh_agency like ('S%') or toh_agency like ('K%') and toh_agency is not missing*/
/*	;*/
/*quit;*/

%macro hex2(n);
            %local digits n1 n2;
            %let digits = 0123456789ABCDEF;
            %let n1 = %substr(&digits, &n / 16 + 1, 1);
            %let n2 = %substr(&digits, &n - &n / 16 * 16 + 1, 1);
            &n1&n2
%mend hex2;
%macro RGB(r,g,b);
            %cmpres(CX%hex2(&r)%hex2(&g)%hex2(&b))
%mend RGB;

/* this macro will look through each TOH agency and pull the data associated then create a rollup report  */
%macro list8;

%local i rep ;
%do i=1 %to %sysfunc(countw(&toh_agency1));
	%let rep = %scan(&toh_agency1, &i);

%put &toh_agency1.;
%put &rep.;

%let font2 = wh;  
%let fonttype = Arial;  

proc sql;
	create table summary_outputIII as
		select distinct
			* from summary_outputII
			where 'toh_agency'n = "&rep." ;

ods excel file="/u/&sysuserid./Medicare Sales Agency Report Rollup_&rep..xlsx"	options (autofilter='all' sheet_name="Pre-Appointment Status");

/* this is the proc report where you define the variables to create the sub reports */
proc report data = summary_outputIII headskip missing /*style=monochromeprinter*/  split='|'
              style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,0,0) background=%RGB(128,128,128) foreground=whitesmoke font_weight=bold];  
 
                   cols 
					'AOR'n 
					'Agency_nm'n
					'Level_Down 3'n
					'Level_Down 2'n
					'TOH_Agency'n
					'WAITING FOR APPLICATION'n 
					'BACKGROUND REQUESTED'n 
					'BGC COMPLETED'n
					'CPM TEAM WORKING ON IT'n 
					'FMO AGENT APPOINTMENT RECEIVED'n 
					'APPOINTMENT COMPLETED'n
					'NOT PROCESSED'n 
					'Total'n;

				define 'AOR'n / left "AGENCY CODE" style(column) =  [tagattr='Format:@'];
				define 'Agency_nm'n / left "AGENCY|NAME" style(column)={cellwidth=5in tagattr='wraptext:no' width=50%};
				define 'Level_Down 3'n  / left "Level 3" style(column) =  [cellwidth=2in tagattr='Format:@'];
				define 'Level_Down 2'n  / left "Level 2" style(column) =  [cellwidth=2in tagattr='Format:@'];
				define 'TOH_Agency'n / left "TOH AGENCY" style(column) =  [cellwidth=2in tagattr='Format:@'];
				define 'WAITING FOR APPLICATION'n / right "WAITING FOR|APPLICATION" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				define  'BACKGROUND REQUESTED'n / right "BACKGROUND |REQUESTED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				define 'BGC COMPLETED'n / right "BACKGROUND |COMPLETED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				define 'CPM TEAM WORKING ON IT'n / right "CPM TEAM|WORKING ON IT" format=comma8. style(column)=[cellwidth=3in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				define 'FMO AGENT APPOINTMENT RECEIVED'n / right "FMO AGENT|APPOINTMENT |RECEIVED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				define 'APPOINTMENT COMPLETED'n / right "APPOINTMENT|COMPLETED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'] ;
				define 'NOT PROCESSED'n / right "NOT |PROCESSED" format=comma8. style(column)=[cellwidth=4in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				define Total / right  "Total" format=comma8. style(column)=[cellwidth=1.5in TAGATTR='format:###,###,##0;[Red]\(###,###,##0)'];
				rbreak after/summarize  style=[fontweight=bold];
compute after;
     'AOR'n = 'Total';
          call define(_row_,'style','style=[cellwidth=1in fontweight=bold bordercolor=lightgray backgroundcolor=lightgray foreground=black]');
                   endcomp;
						run;

proc sql;
	create table AHIPNeeded2 as
		select distinct
			* from AHIPNeeded 
			where 'toh_agency'n = "&rep.";

ods excel options(sheet_name="AHIP Needed" autofilter='all')

proc report data = AHIPNeeded2 headskip missing /*style=monochromeprinter*/  split='|'
              style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,0,0) background=%RGB(128,128,128) foreground=whitesmoke font_weight=bold];  
columns 'Agent Name'N toh_agency assigned_broker_manager SALS_AGET_CD 'Agency Name'n ;
define 'Agent Name'N / left "Agent Name" style(column)={cellwidth=2in};
define 'TOH_Agency'n / left "TOH Agency" style(column)={cellwidth=2in};
define 'assigned_broker_manager'n / left "Broker Manager" style(column)={cellwidth=2in};
define 'agent_individual_writing_number'n / left "Agent Writing Number" style(column)={cellwidth=2in};
define 'Agency Name'N / left "Agency Name" style(column)={cellwidth=2in};

proc sql;
	create table READYTOTRAIN2 as
		select distinct
			* from READYTOTRAIN
			where 'toh_agency'n = "&rep.";

ods excel options(sheet_name="Ready To Train" autofilter='all')

proc report data = READYTOTRAIN2 headskip missing /*style=monochromeprinter*/  split='|'
style(header) = [font=("&fonttype.",11pt) vjust=middle just=center bordercolor=%RGB(0,0,0) background=%RGB(128,128,128) foreground=whitesmoke font_weight=bold];  
columns 'Agent Name'N toh_agency assigned_broker_manager SALS_AGET_CD 'Agency Name'n ;
define 'Agent Name'N / left "Agent Name" style(column)={cellwidth=2in};
define 'TOH_Agency'n / left "TOH Agency" style(column)={cellwidth=2in};
define 'assigned_broker_manager'n / left "Broker Manager" style(column)={cellwidth=2in};
define 'agent_individual_writing_number'n / left "Agent Writing Number" style(column)={cellwidth=2in};
define 'Agency Name'N / left "Agency Name" style(column)={cellwidth=2in};

Re: How to change the header column and font color in ods

LMSSAS — Mon, 04 Apr 2022 21:16:59 GMT

@ballardw please DISREGARD my request for you to view my code. I actually got the program to run successfully!!

Re: How to change the header column and font color in ods

ballardw — Mon, 04 Apr 2022 21:31:56 GMT

@LMSSAS wrote:

@ballardw please DISREGARD my request for you to view my code. I actually got the program to run successfully!!

Good to know.

A lot of programming experience comes from trial and, frequently, error.

topic Re: How to change the header column and font color in ods in SAS Programming

How to change the header column and font color in ods

Re: How to change the header column and font color in ods

Re: How to change the header column and font color in ods

Re: How to change the header column and font color in ods

Re: How to change the header column and font color in ods