Procc Print add Total Row and Top and bottom underline on cell

[SASnewbie2]

Hi 

I'd like to insert a  "Total" on the last row of Map_Geometry and insert a Top & Bottom Underline Style on the Total of Count Cell as shown below. 

Appreciate anyone of you could advise the code. 

proc print data = test noobs;
ods excel options(autofilter="1-5" sheet_name = "Terminal Comparison" embedded_titles='yes');
sum country;
format country dollar12.2; 
run;
title j = l "Terminal Comparisons";
title2 j = l "January 1 - February 1, 2017";

run;

Thanks 

[ed_sas_member]

Hi @SASnewbie2 

Using proc print, you can add a style option to specify a border on the summary cell:

sum country / style(grandtotal)={borderbottomstyle=solid bordertopstyle=solid};

You can also specify the color and many other attributes (e.g. borderbottomcolor = black)

I am not sure that you can add "Total" using PROC Print.

Proc report should be the best option to achieve this.

Could you please share a portion of the data in regular datalines so that we can show you the code?

Best,

[SASnewbie2]

Hi @ed_sas_member

Appreciate your advice.  It works for the borderline style. However, may I know the code for inserting a Total on the last row of Name ?

and I trying to update Title as " 

to show "The First Five Observations Out of 19 as at the generation date, but the result doesn't work out.

 

proc print data=sashelp.class(obs=5) noobs;
ods excel options(autofilter="1-5" sheet_name = "Terminal Comparison" embedded_titles='yes');
sum Age /style(grandtotal)={borderbottomstyle=solid bordertopstyle=solid};
title j = l "The First Five Observations Out of 19 as at %TRIM(%QSYSFUNC(DATE(), NLDATE20.))";
run;
ods excel close;
run;

[ed_sas_member]

Hi @SASnewbie2 

Please try the following code, using PROC REPORT, to add "total" in the first column.

I am not sure to understand what you want to display in the title after "as". Could you please specify?

Best,

ODS EXCEL OPTIONS(AUTOFILTER="1-5" SHEET_NAME = "TERMINAL COMPARISON" EMBEDDED_TITLES='YES');

proc report data=sashelp.class (obs=5) nowd style(header)={backgroundcolor=lightblue fontweight=bold};

	title j = left "THE FIRST FIVE OBSERVATIONS OUT OF 19 AS AT %TRIM(%QSYSFUNC(DATE(), NLDATE20.))";

	column Name	Sex	Age	Height Weight;
	
	define Name / display;
	define Sex / display;
	define Age / analysis;  /* <--- specify analysis if you want to compute the total */
	define Height / display;
	define Weight / display;
	
	rbreak after / summarize style={backgroundcolor=lightblue};
	
	compute after;
		if _BREAK_ = "_RBREAK_" then do;
			Name="Total";
			/* Specify _c3_ as "age" is the 3rd column in the report */
			call define ('_c3_','style','style={BORDERBOTTOMSTYLE=SOLID BORDERTOPSTYLE=SOLID}');
		end;
	endcomp;
	
run;
ODS EXCEL CLOSE;

[ed_sas_member]

Hi @SASnewbie2 

If you want to stick to PROC PRINT, the only way to display 'Total' is to put it as a label in the observation number column:

proc print data=sashelp.class (obs=5)
	grandtotal_label="Total";
	sum age / style(grandtotal)={borderbottomstyle=solid bordertopstyle=solid borderbottomcolor=red bordertopcolor=red};
run;

[SASnewbie2]

Hi  ed_sas_member , 

Appreciate your advice.

The reason I stick to proc print as I've more 10 variables on my dataset.

Can you help me to check on my follows code as I would like to show the date as One day before "Today -1"

	title j = left "THE FIRST FIVE OBSERVATIONS OUT OF 19 AS AT %TRIM(%QSYSFUNC(Today(),-1, NLDATE20.))";

ERROR: Expected close parenthesis after macro function invocation not found.
ERROR: More positional parameters found than defined.

Appreciate you can show me how can I use DO LOOP statement to spilt the dataset by Name and save it to excel worksheet and output name as the "Column Name"

ODS EXCEL OPTIONS(AUTOFILTER="1-5" SHEET_NAME = "TERMINAL COMPARISON" EMBEDDED_TITLES='YES');

proc report data=sashelp.class (obs=5) nowd style(header)={backgroundcolor=lightblue fontweight=bold};

title j = left "THE FIRST FIVE OBSERVATIONS OUT OF 19 AS AT %TRIM(%QSYSFUNC(Today(), NLDATE20.))";

	column Name	Sex	Age	Height Weight;
	
	define Name / display;
	define Sex / display;
	define Age / analysis;  /* <--- specify analysis if you want to compute the total */
	define Height / display;
	define Weight / display;
	
	rbreak after / summarize style={backgroundcolor=lightblue};
	
	compute after;
		if _BREAK_ = "_RBREAK_" then do;
			Name="Total";
			/* Specify _c3_ as "age" is the 3rd column in the report */
			call define ('_c3_','style','style={BORDERBOTTOMSTYLE=SOLID BORDERTOPSTYLE=SOLID}');
		end;
	endcomp;
	
run;
ODS EXCEL CLOSE;

Thanks in advance

[ed_sas_member]

Hi @SASnewbie2 

You can update your title statement as follows, using th INTNX() function to retrieve the day before today:

title j = left "THE FIRST FIVE OBSERVATIONS OUT OF 19 AS AT %left(%qsysfunc(intnx(day,%sysfunc(Today()),-1),NLDATE20.))";

I have used %left rather than %trim to remove leading blanks.

To split the dataset by Name and save each report in a separate worksheet, there is no need to use do loop statement.  You can leverage the "BY" statement as follows:

- add a BY statement in the PROC REPORT

proc report data=sashelp.class (obs=5) nowd style(header)={backgroundcolor=lightblue fontweight=bold};
	by name notsorted;

- Add SHEET_INTERVAL = "bygroup" in the ODS EXCEL options:

ODS EXCEL OPTIONS(AUTOFILTER="1-5" SHEET_INTERVAL = 'bygroup' EMBEDDED_TITLES='YES');

Best,

[SASnewbie2]

Hi ed_sas_member, 

Thanks for your advice.

It works well. However, I would like to save it to separate workbook by the Name (eg. Alfred as at May 2020, Alice as at May 2020)

ODS EXCEL OPTIONS(AUTOFILTER="1-5" SHEET_INTERVAL = 'bygroup' EMBEDDED_TITLES='YES');

Thanks in advance.

[ed_sas_member · Accepted solution]

HI @SASnewbie2 

To generate as many Excel workbooks as names, I recommend that you first put the PROC REPORT in a macro program, and then that you automatize the macro calls by using CALL EXECUTE.

-> The macro program filter the values based on the variable name (cf. WHERE statement). The value of name as well as the day before today are used in Excel file name.

-> The DATA _NULL_ step leverages CALL EXECUTE to avoid writing as many macro calls as name values, and automatize this process:

%macro myreport (name);

	%let date = %TRIM(%QSYSFUNC(Today(), NLDATE20.));

	ODS EXCEL FILE="/home/u41058973/sasuser.v94/&name._&date..xlsx";

	ODS EXCEL OPTIONS(AUTOFILTER="1-5" SHEET_NAME = "TERMINAL COMPARISON" EMBEDDED_TITLES='YES');
	
	proc report data=sashelp.class (obs=5) nowd style(header)={backgroundcolor=lightblue fontweight=bold};
		
		title j = left "THE FIRST FIVE OBSERVATIONS OUT OF 19 AS AT &date.";
	
		where name = "&name.";
		
		column Name	Sex	Age	Height Weight;
		
		define Name / display;
		define Sex / display;
		define Age / analysis;  /* <--- specify analysis if you want to compute the total */
		define Height / display;
		define Weight / display;
		
		rbreak after / summarize style={backgroundcolor=lightblue};
		
		compute after;
			if _BREAK_ = "_RBREAK_" then do;
				Name="Total";
				/* Specify _c3_ as "age" is the 3rd column in the report */
				call define ('_c3_','style','style={BORDERBOTTOMSTYLE=SOLID BORDERTOPSTYLE=SOLID}');
			end;
		endcomp;
		
	run;

	ODS EXCEL CLOSE;

%mend;

data _null_;
	set sashelp.class (obs=5 keep=name); /* <- 1 observation per name */
	call execute (cats('%myreport(',name,')')); /* <- CALL EXECUTE execute the proc report for each value of name */
run;

[SASnewbie2]

Hi ed_sas_member

Great thanks for your advice.

I shall study and apply it on my project.

Thanks a lot.

[ed_sas_member]

You’re welcome @SASnewbie2 😊