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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.