BookmarkSubscribeRSS Feed
TTNY
Calcite | Level 5

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.

 

proc_report_with_lines_below.jpg

 

 

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;

 

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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

style_lines_report.png

TTNY
Calcite | Level 5

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
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2685 views
  • 0 likes
  • 2 in conversation