topic Re: Formating of line statements in proc report in ODS and Base Reporting

Formating of line statements in proc report

TTNY — Wed, 08 Nov 2017 10:31:17 GMT

I use ods tagset and proc report to make an Excel file with below table. After the table I add some lines with the line statement. However it seems the formatting on my 'lines' is different from the rest of the table. I have tried different versions of 'style(line)' to try and make up for this, but I cannot find anything that works. I would like to change the cell height and the border colour. Please advice.

ods _all_ close;

ods noresults ;

ods tagsets.ExcelXP style=seaside file="&OUTDIR.\TBL_31_&co_date..xls"

options(sheet_name="TBL_31_&co_date."

width_points = '12'

width_fudge = '.0625'

absolute_column_width = '200,200,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100'

row_heights='30,30');

proc report missing data=uniquedata_soc_ord style(header)=[background=white] split='*';

column ("Number of adverse drug reactions by preferred term from post-marketing sources - &product_alias. ®"

('' ('' INTL_ORD_CODE soc )) ('' ('' pt))

('Spontaneous, including competent authorities *(worldwide) and literature'

('Serious' per_ser_s1 cum_ser_s1) ('Non-serious' per_nonser_s1 cum_nonser_s1))

('Total *Spontaneous'('' tot))

('Non-interventional post-*marketing studies and *reports from other *solicited sources'

('Serious' per_ser_s2 cum_ser_s2)));

define INTL_ORD_CODE / group order=formatted noprint ;

define soc / group 'SOC' style(header)=[just=l];

define pt / group 'PT' style(header)=[just=l];

define per_ser_s1 / analysis 'PSUR *period';

define cum_ser_s1 / analysis 'Cumulative';

define per_nonser_s1 / analysis 'PSUR *period';

define cum_nonser_s1 / analysis 'Cumulative';

define tot / analysis 'Cumulative *all';

define per_ser_s2 / analysis 'PSUR *period';

define cum_ser_s2 / analysis 'Cumulative';

break after soc / summarize style=[font_weight=bold font_style=roman];

compute after soc;

soc = "";

pt = "Total";

endcomp;

rbreak after / summarize style=[font_weight=bold font_style=roman];

compute after;

soc = "Total";

%macro add_lines();

%if &COUNTRY_COUNT ne 0 %then %do;

line "" ;

line "Report based on" ;

line "%sysfunc(trim(&psur_path.))\" ;

line "&BASE_DATA. extracted on &cr_date.";

line "" ;

line "Table created on %sysfunc(putn(%sysfunc(date()),date9.)) %sysfunc(putn(%sysfunc(time()),hhmm.))";

line "" ;

line "Table generated for the country: &COUNTRIES.";

line "" ;

line "MedDRA version: &MDVER.";

line "" ;

line "Note: Empty SOC and PT denotes as No Data Found";

%end;

%else %do;

line "" ;

line "Report based on" ;

line "%sysfunc(trim(&psur_path.))\" ;

line "&BASE_DATA. extracted on &cr_date.";

line "" ;

line "Table created on %sysfunc(putn(%sysfunc(date()),date9.)) %sysfunc(putn(%sysfunc(time()),hhmm.))";

line "" ;

line "MedDRA version: &MDVER.";

line "" ;

line "Note: Empty SOC and PT denotes as No Data Found";

%end;

%mend;

%add_lines();

endcomp;

run;

ods tagsets.ExcelXP close;

ods listing ;

ods results;

Re: Formating of line statements in proc report

Cynthia_sas — Wed, 08 Nov 2017 15:52:00 GMT

Hi, without getting into making data and using your macro and all your lines, the simplest way to impact the lines is either in the PROC REPORT statement or down on the COMPUTE statement, as shown in my example below. Remember that every LINE statement is creating a separate cell, so your STYLE applies to every cell. I liked the look of ODS EXCEL output better than TAGSETS.EXCELXP output.

cynthia

Re: Formating of line statements in proc report

TTNY — Thu, 09 Nov 2017 08:12:43 GMT

The HEIGHT statement does not seem to have any impact on cell height and nothing in your code seem to relate to border colour. I need to use TAGSETS.EXCELXP because other parts of my code is depending on it. So this did not help, sorry to say. But thanks for trying.

Re: Formating of line statements in proc report

Cynthia_sas — Thu, 09 Nov 2017 21:21:59 GMT

Hi: I was not directly solving your issue, I was showing you where to put the style attributes if you waned to play around with borders. The border style attributes are well documented so if putting them in the right place doesn’t work, you can still try changing the style template.

Cynthia

Re: Formating of line statements in proc report

_Hopper — Mon, 09 Jun 2025 20:47:30 GMT

Can you apply different styles on the same line statement?

Re: Formating of line statements in proc report

Ksharp — Tue, 10 Jun 2025 01:23:20 GMT

ods excel file='c:\temp\temp.xlsx';
proc report data=sashelp.class nowd;
column _all_;
compute after ;
line "(*ESC*){style [color=red]xxxxxxx} (*ESC*){style [color=blue fontweight=bold]yyyyyy}  zzzzzz";
endcomp;
run;
ods excel close;

Re: Formating of line statements in proc report

Tom — Tue, 10 Jun 2025 01:43:50 GMT

That code also works with ods tagsets.ExcelXP .

Re: Formating of line statements in proc report

Ksharp — Tue, 10 Jun 2025 01:54:39 GMT

Dear Tom,
"ods tagsets.excelxp" is obsoleted totally.
We don't use it anymore, the "ods excel" has replaced it.

Re: Formating of line statements in proc report

SASKiwi — Tue, 10 Jun 2025 03:01:15 GMT

@Ksharp - For those on old maintenance releases like SAS 9.4M1/M2 the EXCELXP tagset is still a better option as ODS EXCEL wasn't mature back then...🙂

Re: Formating of line statements in proc report

_Hopper — Tue, 10 Jun 2025 18:29:47 GMT

What I need is a method to apply different background colors to different words on the same line while maintaining the same color text. Is this possible? It seems the background color only applies to the line as a whole rather than each individual word. The final destination is either pdf or rtf.

Re: Formating of line statements in proc report

Kathryn_SAS — Tue, 10 Jun 2025 18:52:09 GMT

In ODS, the Line statements in a Compute block are seen as one long string. The same is true if there are multiple Line statements in a Compute block. Some style attributes, such as Background, apply to the Line statement cell as a whole and not to individual parts.

Re: Formating of line statements in proc report

Ksharp — Wed, 11 Jun 2025 01:38:12 GMT

You want this ?

ods rtf file='c:\temp\temp.rtf';
proc report data=sashelp.class nowd;
column _all_;
compute after/style(lines)={color=black} ;
line "(*ESC*){style [background=red]xxxxxxx} (*ESC*){style [background=blue]yyyyyy}  zzzzzz";
endcomp;
run;
ods rtf close;

Re: Formating of line statements in proc report

Kathryn_SAS — Wed, 11 Jun 2025 12:01:38 GMT

Unfortunately, neither of the Excel destinations (Excel or Tagsets.ExcelXP) honor the background color in a LINE statement. It can only be applied on the Compute statement, so it applies to the whole cell.

ods listing close;
ods excel file='c:\temp\temp.xlsx';
ods tagsets.excelxp file='c:\temp\test.xml';
proc report data=sashelp.class nowd;
column _all_;
compute after/style(lines)={color=black backgroundcolor=yellow} ;
line "(*ESC*){style [background=red]xxxxxxx} (*ESC*){style [background=blue]yyyyyy}  zzzzzz";
endcomp;
run;
ods _all_ close;
ods listing;

Re: Formating of line statements in proc report

Ksharp — Thu, 12 Jun 2025 01:59:48 GMT

Yeah . It did not honer EXCEL destination.
But _Hopper want a PDF or RTF file .