ods proc report insert line non spanning

[lid9zkr]

Hello All.

I am creating a report that i would like to insert a row that doesn't span across the report as I am highlighting a column and it is causing a break in the highlighting.  I have tried a ^n but this creates a new line within a line.  Basically, I need a unmerge of the line.

Any help would be appreciated.

ods _all_ close;
ods noresults; /*closes resluts window and directs output to file */

ods excel file="&file_PG"
options(start_at= "1,1" flow = "table" absolute_column_width= "30,14,12,8,8,8,8,8,8,8,8,8,8,8" row_heights="20,15" sheet_name = "PG"
sheet_interval="none" embedded_titles="yes" embedded_footnotes='yes' ) style=Styles.sasdocprinter;
ODS ESCAPECHAR='^';
/*Creates report */
title1 j = left color = black font= 'arial narrow' h=2 "Comcast Corporation";
title2 j = left color = black font= 'arial narrow' h=2 "Rep: Sheila Stohler";
title3 j = left color = black font= 'arial narrow' h=2 "Svc Rep Addr: Harrisburg, PA";
title4 j = left color = black font= 'arial narrow' h=2 "Effective Date: 07/01/2003";
title5 j = left color = black font= 'arial narrow' h=2 "Dedicated units: Yes";
title6 j = left color = black font= 'arial narrow' h=2 "Customer service handled in: Harrisburg";
title7 j = left color = black font= 'arial narrow' h=2 "Claims processed in: Harrisburg";
title8 j = left color = black font= 'arial narrow' h=2 "Frequency of reporting: Quarterly";
title9 j = left color = black font= 'arial narrow' h=2 "Frequency of settlement: Annual";
title10 j = left color = black font= 'arial narrow' h=2 "Maximum for all penalties: Not to Exceed 50% of ASO Fee";

/*proc odstext;*/
/* p "Comcast Corporation " ;*/
/* p "Rep: Sheila Stohler";*/
/* p "Svc Rep Addr: Harrisburg, PA";*/
/* p "Effective Date: 07/01/2003";*/
/* p "Dedicated units: Yes";*/
/* p "Customer service handled in: Harrisburg";*/
/* p "Claims processed in: Harrisburg";*/
/* p "Frequency of reporting: Quarterly";*/
/* p "Frequency of settlement: Annual";*/
/* p "Maximum for all penalties: Not to Exceed 50% of ASO Fee";*/
/**/
/*run;*/

proc report data = comcast
style(column) = {font_face='Arial' fontsize = 10pt}
style(header) = {font_face='Arial' fontsize = 10pt};

column Operations Measurement_Description MEASURE PENALTY Source jan_21 feb_21 mar_21 Q1 apr_21 may_21 jun_21 Q2 jul_21 aug_21 sep_21 Q3;
define Operations / order noprint width=30 style(column) = {tagattr="wrap:no" JUST=l};;
define Measurement_Description / 'Measurement' /*style(header) = {tagattr="wrap:no"}*/;
define MEASURE / display 'Measure' noprint;
define PENALTY / display 'Penalty' ;
define Source / display 'Source' style(column) = {font_face='Arial' fontsize = 10pt};
define jan_21 / display ;
define feb_21 / display ;
define mar_21 / display ;
define Q1 / display style ={background=CXD9E1F2 BORDERleftSTYLE= solid BORDERrightSTYLE= solid} style(header) ={BORDERtopSTYLE= solid BORDERrightSTYLE= solid BORDERleftSTYLE= solid} ;
define apr_21 / display ;
define may_21 / display ;
define jun_21 / display ;
define Q2 / display ;
define jul_21 / display ;
define aug_21 / display ;
define sep_21 / display ;
define Q3 / display ;

compute Q1;
call define(_col_,'style','style={background=CXD9E1F2 }');
endcomp;

compute before Operations/style = data{just =l fontweight =bold font_face='Arial' fontsize = 10pt };
line Operations $30. ;
endcomp;

compute after Operations/style=data{just = l background=CX0070C0 };
line '';
call define(_col_,'style','style={tagattr="unmerge"}');
endcomp;

compute Measurement_Description;
if upcase(substr(Measurement_Description,1,23)) = "AVERAGE SPEED TO ANSWER" then do;
call define(_row_,'style','style={tagattr="format:#,###"}');
end;
endcomp;
RUN;

ods excel close;

[ballardw]

No example data so I won't even try.

On this link: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/p14xegao6xt0xnn1865r422tpytw.htm read the section on

Order of Precedence When Applying Style Attributes to Data Cells

I suspect that your report code has been edited somewhat. Likely with stuff related to Measurement and Measurement_description variables that has something to do with the row that is overwriting the column.

Minor point, you might also want to consider setting OPTIONS MISSING=' '; space for the missing character, before report to remove the dots in the  empty cells just to make the report more readable.

You might consider editing your post so names, such as those companies and people are anonymized.

[Cynthia_sas]

Hi:
Any output from the LINE statement will span the entire table, from left edge to right edge of the table. You cannot restrict a LINE statement to be only under selected columns.
Cynthia

[lid9zkr]

Thanks Cynthia.

Is there an alternative method that would accomplish putting a field item on a single row without spanning?

I appreciate your efforrs.

[Cynthia_sas]

Hi:
It looks like your desired destination is ODS EXCEL. I'd be very tempted to do BY group processing for your variable that holds the values Account Management, Claims Processing and Customer Service and make a separate "mini-table" for each of these values. With BY Group processing you can put the BY group variable above the table and with sheet_interval='none', you could get all 3 mini-tables on one sheet.

Cynthia

[lid9zkr]

thanks Cynthia.  I may give that a try.

[ballardw]

---

@lid9zkr wrote:

Thanks Cynthia.

Is there an alternative method that would accomplish putting a field item on a single row without spanning?

I appreciate your efforrs.

---

I've done some pretty ugly code that involves precalculating everything and inserting key values for the "rows" that are to be blank that will not appear in actual data coupled with custom format(s) to set the displayed text to a blank and left/right borders to zero width.

But my output destination was not Excel so I am not sure how well it would translate and I consider the code a bit of a kludge as it is not easy to make minor changes to anything as it involves style overrides is basically every cell.