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;
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
Additionally you need TITLE and/or FOOTNOTE statements.
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.