while exporting multiple datasets from library to excel how can i have titles as well in the sheets

noda6003 — Fri, 05 Mar 2021 11:20:05 GMT

I am trying to export multiple datasets in work library to excel with different sheets and it works fine, But i also want to have titles in top of each sheet. tried proc print but didnt work, any help on how to get it.

My sample code


/*-Check for identifying missing LBSTRESC records while LBSTRESU is not
missing-*/
proc sort data =sdtm.lb
	(where=((LBSTRESC eq "" and LBSTRESU ne "") or (LBORRES eq "" and
	LBORRESU ne "" ))) tagsort nodupkey
	out = check12(keep = LBTEST LBTESTCD LBSTRESU LBORRESU LBORRES LBSTRESC);
	by LBTEST LBTESTCD LBSTRESU LBORRESU;
run;

data check12;
	set check12;
	by LBTEST LBTESTCD;

	if first.LBTEST ne last.LBTEST then
		put "ALERT_R: Units are populated while results are missing" LBTEST
		"Requires review";
run;
title1 "Check for identifying missing LBSTRESC records while LBSTRESU is not
missing";
proc pint data=check12; run;

/*-Check for identifying missing LBORRESU records while LBORRES is provided*/
data check13;
	set sdtm.lb;
	lborresn = input(lborres,??best.);

	if lborresn ne . and lborresu = ' ';
	put "Alert_R:" USUBJID " have records with reported result that has numeric
		result values but do not have corresponding unit";
run;


proc template;
	define style mystyle;
		parent = styles.default
		;
		style body from body /
			topmargin=.5in
			bottommargin=.5in
			leftmargin=.5in
			rightmargin=.5in
		;
		style table from table /
			foreground = black
			font_face = 'arial'
			font_size = 10pt
			just = center
			font_weight = bold
			borderwidth = 1
			vjust = center
			bordercolor = black
		;
		style header from header /
			foreground = black
			font_face = 'arial'
			font_size = 10pt
			just = center
			font_weight = bold
			borderwidth = 1
			vjust = top
			bordercolor = black
		;
		style data from data /
			background = white
			foreground = black
			font_face = 'arial'
			vjust = center
			just = center
			font_size = 10pt
			borderwidth = 1
			bordercolor = black
		;
	end;
quit;

%macro excel (lib=,file_loc=);

	proc sql noprint;
		select count(memname) into: memname_cnt
			from dictionary.members
				where upcase(libname)="&lib";
		select memname into: sasdata1 - : sasdata%eval(&memname_cnt)
			from dictionary.tables
				where upcase(libname)="&lib"
					and upcase(memname) not in ('FREEZE_DATE','LOG','TEMP');
		%let num_sasdata=&sqlobs;
	quit;

	ods tagsets.excelxp file="&file_loc"
		options(autofit_height='yes' wraptext='no' frozen_headers='1' row_repeat='1'
		orientation='landscape' column_repeat='1' center_horizontal='yes'
		absolute_column_width='20') style=mystyle;

	%do x = 1 %to &num_sasdata;
		ods tagsets.excelxp options(sheet_name="&&sasdata&x");

		proc report data=&lib..&&sasdata&x nowd missing;
		run;

		quit;

	%end;

	ods tagsets.excelxp close;
	ods listing;
%mend;

Re: while exporting multiple datasets from library to excel how can i have titles as well in the she

PhilC — Fri, 05 Mar 2021 13:00:43 GMT

In your options for ODS ... EXCELXP consider using these options:

EMBEDDED_FOOTNOTES	Put footnotes in the worksheet
EMBEDDED_TITLES	Put titles in the worksheet

See Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset | SAS Support

Re: while exporting multiple datasets from library to excel how can i have titles as well in the she

ballardw — Fri, 05 Mar 2021 16:22:03 GMT

Additionally you need TITLE and/or FOOTNOTE statements.

topic Re: while exporting multiple datasets from library to excel how can i have titles as well in the she in SAS Programming

while exporting multiple datasets from library to excel how can i have titles as well in the sheets

Re: while exporting multiple datasets from library to excel how can i have titles as well in the she

Re: while exporting multiple datasets from library to excel how can i have titles as well in the she