topic Re: Proc Report: conditionally assigning STYLE attributes to a specific column observations in SAS Programming

Proc Report: conditionally assigning STYLE attributes to a specific column observations

A_Kh — Thu, 09 Mar 2023 03:24:53 GMT

Dear Experts,

I'm trying to output my Proc report results into .xml file using ODS tagsets.excelxp. Ideally my output table should look like as below (with proper indentation). However, my code is producing indentation only for the first occurrence of each value, that is specified in Compute Block (code will be attached below). This also messing up original order of data regardless define/order=data option.
So, I need your expertise here to figure out how to properly instruct sas for desired outcome.

Thank you!

data have;
length Label $35; 
input Label $ N cat1 cat2 cat3;
datalines;
Baseline 310 200 100 10
Female 170 . . .
Male 140 . . .
Month8 308 199 99 12
Change_from_Baseline 2 1 1 -2
Female 2 . . .
Male 0 . . .
Month12 304 194 90 20  
Change_from_Baseline 6 6 10 -10
Female 4 . . .
Male 2 . . .
Month16 300 190 85 15
Change_from_Baseline 10 10 15 -5
Female 7 . . .
Male 3 . . .
;
run; 

ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing
	options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1');
	proc report data= have style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt}
								 			style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange;
		column label N cat1 cat2 cat3;
		define Label/ "" width=25 order order=data;
		define N/ "Total" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat1/ "Excellent" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat2/ "Good" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat3/ "Fair" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		compute label;
			if strip(label) eq "Change_from_Baseline" then  call define(_col_, "style", "style=[indent=3 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");
			else if strip(label) in ("Female", "Male") then  call define(_col_, "style", "style=[indent=6 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");
			else call define(_col_, "style", "style=[just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");
		endcomp;
	run; 
ods tagsets.excelxp close;

Expected Result

Re: Proc Report: conditionally assigning STYLE attributes to a specific column observations

ballardw — Thu, 09 Mar 2023 04:56:56 GMT

Here is one way to do the indent. The custom format provides additional blanks for different values. The STYLE={asis=on} overrides the standard behavior of removing leading blanks and the format assignment says to use the format for display so the spaces are available. This approach can require more spaces than you think as proportional fonts can reduce the space used.

You may not want the variable Label as an order variable.

proc format;
value $labelindent
"Change_from_Baseline" = "  Change_from_Baseline"
"Female"               = "    Female"
"Male"                 = "    Male"
;
run;

	proc report data= have style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt}
								 			style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange;
		column label N cat1 cat2 cat3;
		define Label/ "" width=25  order=data format= $labelindent. style={asis=on};
		define N/ "Total" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat1/ "Excellent" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat2/ "Good" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat3/ "Fair" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
/*		compute label;*/
/*			if strip(label) eq "Change_from_Baseline" then  call define(_col_, "style", "style=[indent=3 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");*/
/*			else if strip(label) in ("Female", "Male") then  call define(_col_, "style", "style=[indent=6 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");*/
/*			else call define(_col_, "style", "style=[just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");*/
/*		endcomp;*/
	run;

Re: Proc Report: conditionally assigning STYLE attributes to a specific column observations

A_Kh — Thu, 09 Mar 2023 14:21:51 GMT

@ballardw , proc format might change data format in other destinations like html, but it seems to have no influence over .xml outputs.

Recently, @IyenJ helped me with formatting .xml cells using "Style(column)={TAGATTR='format:0.00'}" (https://communities.sas.com/t5/SAS-Programming/Proc-Report-to-ODS-EXCEL-xml-issue-in-writing-last-0-after-the/td-p/859800).
Based on your suggestion and above experience I've tried to use {tagattr= "format: $labelindent."}. This creates output in Results window without format, but not creating any .xml output.

ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing
	options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1');
	proc report data= have style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt}
								 			style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} out=report_scorechange;
		column label N cat1 cat2 cat3;
		define Label/ "" width=25 style(column)= {just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt TAGATTR='format: $labelindent.'} display;
		define N/ "Total" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat1/ "Excellent" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat2/ "Good" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat3/ "Fair" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
	run; 
ods tagsets.excelxp close;

Re: Proc Report: conditionally assigning STYLE attributes to a specific column observations

A_Kh — Tue, 14 Mar 2023 18:27:50 GMT

Dear Community,
Just wanted to follow up on this question as I received the solution from SAS Tech Support Team.

The solution was pretty simple - adding an extra numeric variable to the dataset HAVE (order=_n_) then define this variable in proc report as internal ordering variable (define order / order order=internal noprint;).

The full code is below:

data have;
	set have;
	order=_n_;
run;

ods tagsets.excelxp file = "C:\Users\Desktop\test\Score_tables.xml" style=listing
	options(sheet_name= "Table 3 - Categorical" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='24,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1');
	proc report data= have style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt}
							style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} ;
		column order label N cat1 cat2 cat3;
		define order / order order=internal noprint;
		define Label/ " " width=25 order ;
		define N/ "Total" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat1/ "Excellent" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat2/ "Good" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
		define cat3/ "Fair" center width=8 style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
			compute label;
				if strip(label) eq "Change_from_Baseline" then  call define(_col_, "style", "style=[indent=3 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");
				else if strip(label) in ("Female", "Male") then call define(_col_, "style", "style=[indent=6 just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");
				else call define(_col_, "style", "style=[just=l font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt]");
			endcomp;
	run;
ods tagsets.excelxp close;

The obtained result is:

Hope this will be helpful to others too.
Thank you, all, for taking time to answer the question!