Formating of line statements in proc report

[TTNY]

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;

[Cynthia_sas]

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

[TTNY]

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.

[Cynthia_sas]

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

[_Hopper]

Can you apply different styles on the same line statement?

[Ksharp]

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;

[Tom]

That code also works with ods tagsets.ExcelXP .

[Ksharp]

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

[SASKiwi]

@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...🙂

[_Hopper]

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.

[Kathryn_SAS]

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.

[Ksharp]

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;

[Kathryn_SAS]

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;

[Ksharp]

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