@ballardw  this is great! This will work too, and thank you for the clarification on how the code works.  However, I forgot to mention, in the example i gave the header started with "level" so you were able to use the =: to grab the value that started with the word level. But what if the subheadings are different. For example in this one one heading is "level I clinics" and the other heading is "non clinics". How would you suggest we handle that?  can the statement  "if clinic_names =: 'level' then do" change to "if clinic_names in: ('level', 'non clinics') then do".   I think that would work. I tried it in the smaller datasets and I think it worked!   data have; input Clinic_names $20.; datalines; level I clinics Daisy clinic Hewey clinic Dewey OP clinic Mickey House Donald Clinic Non clinics Pluto Hosp Goofy clinic Horace House Minnie clinic ; run; ... View more

wow! an easy solution! I was trying to add the original function within in the %sysfunc but didn't realize that I need to also change the today() and remove the quotes. Thank you!!! ... View more

@mkeintz  Ah! thanks! simple solution!   I have another issue that I hope you are able to assist in this thread. It is kind of related. In the same data run I am creating a proc report that I ODS export, I have a title that currently reads as follows: title2 j=l h=11pt color=black font='Calibri' "^{style[fontweight=bold color=black] Date Complete: %sysfunc(date(),mmddyy10.)}";   The above takes todays date and populates as date complete. How would I change the above so it would take yesterdays date and if yesterday date is a Sunday it would populate Fridays date instead? I thought adding the 'weekday' may work but it didn't.    Thanks! ... View more

@Tom  YOU ARE A GENIUS!!!!!!   All your suggestions were great but the last option worked!!!   You ROCK!! YOU ALL ROCK!!!!   THANK YOU! ... View more

@Tom  Below is my full code, where I added the nobs=0 suggestion.  I did before the set statement as per the code. Not sure what I am doing incorrectly.   ods excel file ='C:\Report.xlsx' ; ods escapechar ='^'; ODS excel options(Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='New Patient'); title1 j=l h=11pt color=black font=Calibri bold "^{style[fontweight=bold] Summary of ^{style[fontstyle=italic] New} Patient by County and Clinic}"; title2 j=l h=11pt color=black font=Calibri "^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}"; data _null_; if nobs=0 then call execute('footnote h=11pt justify=left font=Calibri color=black bold "No data available.";'); set zero_obs nobs=nobs; if index(clinic,'*') then do; call execute ('footnote h=11pt justify=left font=Calibri color=black bold "* Multiple clinics are reported here";'); stop; end; run; proc report data=zero_obs style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold] style(column)={font_face='Calibri' fontsize=11pt}; column County Clinic new_pat ; define County / group; define Clinic / group; define new_pat / sum; rbreak after / summarize style=Header; Compute after; Clinic ='GRAND TOTAL'; endcomp; run; ODS excel options (Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='Existing patients'); title1 j=left color=black font='Calibri' bold "Summary of Existing pateints by clinic"; title2 j=l h=11pt color=black font=Calibri "^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}"; data _null_; set existing_pt ; if index(program_name,'*') then do; call execute ('footnote h=11pt justify=left font=Calibri color=black bold "* Multiple programs are reported here";'); stop; end; run; proc report data=existing_pt style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold] style(column)={font_face='Calibri' fontsize=11pt}; column clinic existing_pt; define clinic / group; define existing_pt / sum; rbreak after / summarize style=Header; Compute after; clinic ='GRAND TOTAL'; endcomp; run; ods excel close; title1 ""; ... View more

Thank you @andreas_lds for the suggestion, unfortunately it didn't work 😞 ... View more

@Tom  Tried the below code it didn't work 😞 I did post the full code that I was using.  Thanks! ... View more

@Tom @japelin    below is the full code that I have that works so far, except the issue I have now.   If the dataset has  0 observations (see "dataset = datanew" in the code below) then it will not export any excel sheet. I should have noted that I am running two serpate proc reports and exporting the restuls in one excel with two sheets. So one dataset will always have some data (see data = data_cum) and it will export to the excel sheet, but the other dataset (datanew) sometimes may have data to export and other times will have 0 observations, at which point there will be no excel sheet produced at all, this dataset is created conditional of the day of the reporting. Suggestions? I will try @japelin suggestions below i wonder if that will work.   ods excel file ='C:\report.xlsx' ; ods escapechar ='^'; ODS excel options(Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='New Patients'); title1 j=l h=11pt color=black font=Calibri bold "^{style[fontweight=bold] Summary of ^{style[fontstyle=italic] New} Patients County and Clinic}"; title2 j=l h=11pt color=black font=Calibri "^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}"; data _null_; set x.datanew ; if index(clinic,'*') then do; call execute ('footnote h=11pt justify=left font=Calibri color=black bold "* Multiple clinics are reported here";'); stop; end; run; proc report data=x.datanew style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold] style(column)={font_face='Calibri' fontsize=11pt}; column County clinic New_patients; define County / group; define clinic / group; define New_patients/ sum; rbreak after / summarize style=Header; Compute after; clinic ='GRAND TOTAL'; endcomp; run; ODS excel options (Embedded_Titles ='yes' embedded_footnotes='yes' sheet_name='Existing patients'); title1 j=left color=black font='Calibri' bold "Summary of Exisiting patients by clinic"; title2 j=l h=11pt color=black font=Calibri "^{style[fontweight=bold]Date Complete: ^{style[color=blue fontstyle=italic]%sysfunc(date(),worddate18.)}}"; data _null_; set x.data_cum ; if index(clinic,'*') then do; call execute ('footnote h=11pt justify=left font=Calibri color=black bold "* Multiple clinic are reported here";'); stop; end; run; proc report data=x.data_cum style(header)=[background=darkmagenta color=white font_face='Calibri' fontsize=11pt fontweight=bold] style(column)={font_face='Calibri' fontsize=11pt}; column clinic cum_pt ; define clinic / group; define cum_pt / sum; rbreak after / summarize style=Header; Compute after; clinic ='GRAND TOTAL'; endcomp; run; ods excel close;   ... View more