ODS, ODSText, Titles

[PhilC]

This is crazy and frustrating; I have tried to recreate a simple set of code to duplicate this but I've failed to.

Using Enterprise guide 8.2

First attempt:

Wrote four macros one for each spreadsheet in the Excel ODS output destination.  On the last page/ spreadsheet a PROC ODSTEXT with several lines of output is used.  Wrote  code to open Excel destination and ran the four macros directing them to new sheets.  The problem, no mater what the title of the ODSTEXT became the previous title from the previous report.  1) I used TITLE;  I used it in the macro, outside the macro and both and then successively (repetitively and redundantly).  I've used SAS for 15 years, it doesn't do this.  2) Reset my system and re-ran the code, same same.

Second attempt: removed macros using ODS DOCUMENT

Wrote four ODS Document blocks one for each spreadsheet of a to-be-opened Excel output destination.  On the last page/spreadsheet, a PROC ODSTEXT with several lines of output is used.  Wrote code to open the Excel destination and replay the DOCUMENT paths directing them to the four sheets.  The problem is still there; the title of the ODSTEXT became the previous title from the previous report that was ran before it, it just happened to be the second page's title due to EG code submission orderring.  1) I used TITLE;  I used it in the ODS document block , outside the ODS document block and both and also successively.  2) I used ODS PROCTITLE=ON and PROCTITLE=OFF in the ODS DOCUMENT block, and also surrounding the PROC DOCUMENT: REPLAY block.   same same.

What else can I try.  What angle is it that I am not seeing.

Something to do with ODSTEXT... 

Thanks, and sorry there's no code.  I'll try again to recreate this.

[ballardw]

If you do not want the Title, I have to assume you meant an actual Title statement since no code is provided, from a previous procedure then clear the title:

Title "some title statement";

proc someproc;

run;

Title ;

The last title clears the title definitions before the next output is generated. If you have used something with 5 title statements you can use

title3;

to clear the definitions of title line 3 and higher and leave the Title1 and Title2 active.

[PhilC]

Great advice!, It's well documented too.  Crazy and frustratingly, it doesn't work in this case.

[ballardw]

---

@PhilC wrote:

Great advice!, It's well documented too.  Crazy and frustratingly, it doesn't work in this case.

---

What case?

Time to show some code.

I've been using this with ODS for years and haven't had a problem with titles other than getting the options set in Excel to take them at all.

[Reeza]

What is your version of SAS, not EG?
PROC PRODUCT_STATUS; RUN;

Prior to 9.4M5 there were some bugs around ODS TEXT stuff that I recall being problematic.

[PhilC]

For Base SAS Software ...
   Custom version information: 9.4_M6
   Image version information: 9.04.01M6P110718
...

[Reeza]

Then I think you do need to show a reproducible example at this point. Hard to know if it's an issue with order of operations, a bug or something else....

[PhilC]

You both are right...  I tried but that code did -not- repeat the same behavior, but  I'll give it another go when I get time.  Thanks again all.  Get back to you soon.

[PhilC]

Here is a EGP file.  I captured the magic some how.  Please let me know how this works for you.  The last spreadsheet, as far as my understanding of SAS goes, should have blank TITLES.  But on my computer, on its last run, the workbook repeats the third spreadsheet title for both the PROC ODSTEXT and PROC PRINT on the fourth sheet , incorrectly.

OK the EGP file is giving the website trouble.  I am changing the name from:

Project bk4 Communities Ex - Copy.egp

to

Project bk4 Communities Ex - Copy.egp.zip

Lets see if this works...  (I can't believe this is still a problem.)

[ballardw]

Better might be to show the generated code.

The gyrations involved in loading someone elses EGP, when libraries aren't defined, paths can't match, files aren't available to read and/or data sets just don't exist makes these difficult.

Won't get into to the different versions of EG and compatibility with the file to start.

[PhilC]

I removed file paths, I changed all procedures to read SASHELP datasets.  My version of EG is 8.2 Update 4 (8.2.4.1261) (64-bit), there is an update to "Update 5" that I haven't installed.  

And I know downloading weird non-text files from the internet is a big creepy, no-no.  I'll see if I can concatenate the code tomorrow.  Gotta go home.

Thanks , ballawrdw

[PhilC]

As I was cutting and pasting this together, I began to worry that it was going to work - BUT - is still doesn't work.  The same dysfunction.  But it is different today, I'll explain after this:

/*New EG Node*/

ODS document name=excel(write);
ODS document close;

  title;footnote;


/*New EG Node*/
 
ODS document name=excel(UPDATE) DIR=(PATH=\Table_1);

  title "Table 1";
  title2 "Lorem ipsum dolor Average Daily Populations"; 
  title3 "and sit amet Rates Per 1,000 consectetur Population ";
  title4 "for 2021*";
  title5 " ";

  %let Caveats="This is an Excel copy of data provided monthly and published at http://adipiscing.elit.Fusce/pub  Refer to the his data's correspondng published report for all caveats to the data.";
  Footnote &Caveats;
  title6 &Caveats;


  proc report data=sashelp.CARS(OBS=50) style(header)=[background=lightgray Font_Weight=Bold] ;
    Column Make	Model	Type	Origin	DriveTrain	MSRP;
  run;
  title;

ODS document;

/*New EG Node*/

ODS document name=excel(UPDATE) DIR=(PATH=\Table_2);

  Title "Table 2";
  Title2 "Mauris Ex Libero, pretium"; 
  Title3 "Nec Ultricies sed,  Report";
  Title4 "for 2021*";				
  Title5 " ";
  Title6 "*The following tristique non nulla did not transmit information on their populations using the instrument developed per In at luctus leo, therefore their contributions can not be represented in statistics above.  For these vitae volutpat mauris, only the Suspendisse vestibulum finibus population is provided.  These  counts are omitted from the total, but not the grand total:";				
  Title7 "Commodo Nulla Sagittis Ligula Quis Vestibulum Vehicula";


  proc report data=sashelp.CARS(OBS=50) style(header)=[background=lightgray Font_Weight=Bold] ;
    Column Make	Model	Type	Invoice	EngineSize	Cylinders	Horsepower	MPG_City	MPG_Highway	Weight;
  run;
  title;

ODS document close;

/*New EG Node*/

ODS document name=excel(UPDATE) DIR=(PATH=\Table_3);

  title "Table 3";
  title2 "&ReportMonthYear Nunc Sed Malesuada Sapien";											
  option missing=" ";

  proc report data=SASHELP.PRDSAL3(OBS=35) style(header)=[background=lightgray Font_Weight=Bold];
    Column COUNTRY	STATE	COUNTY	ACTUAL	PREDICT	PRODTYPE	PRODUCT	YEAR;
  run;
  title;


ODS document close;

/*New EG Node*/

title;footnote;*<---   this title should work;

ODS document name=excel(UPDATE) DIR=(PATH=\Table_3\Caveats);
  data _NULL_;
    if 0 then set SAShelp.Holiday nobs=n;
    if n>1 
      then line="Proin id turpis sit amet neque bibendum rhoncus. Donec elementum convallis quam quis faucibus noted below:";
      else line="";
    call symput("TABL_3_NR_Occs_Caveat",line);
    stop;
    run;
  proc ODStext;
    p "Table 3 Caveats"/style=systemTitle;
    p " ";
    p "Donec pharetra risus sit amet dolor dignissim, vitae luctus lectus lacinia. Vivamus pellentesque facilisis nisi et rhoncus.  ";
    p "";
    p "Donec Odio Tellus Maximus Vel Turpis Vitae Mattis";
    p "";
    p "&TABL_3_NR_Occs_Caveat";
  run;
  title;

  proc print data=SAShelp.Holiday(OBS=7) NoObs;
    var name	desc;
  run;

ODS document close;


/*New EG Node*/

options fmterr;
    title;footnote;

ODS Excel  
  options(
    embedded_titles='ON' 
    embedded_footnotes='ON'
    sheet_interval='NONE'
    /*--------*/
    Frozen_Headers="8"
    SHEET_NAME="Tbl1"
  );

  proc document name=excel ;
    replay \Table_1#1\Report#1\Report#1\Report#1;
  run;

ODS Excel options (
    sheet_interval='NOW'
    Frozen_Headers="9"
    SHEET_NAME="Tbl2"
  );

  proc document name=excel ;
    replay \Table_2#1\Report#1\Report#1\Report#1;
  run;

ODS Excel options (
   sheet_interval='NOW'
    Frozen_Headers="4"
    SHEET_NAME="Tbl3a"
  );

  proc document name=excel ;
    replay \Table_3#1\Report#1\Report#1\Report#1;
  run;

ODS Excel options (
   sheet_interval='NOW'
    Frozen_Headers="0"
    SHEET_NAME="Tbl3b"
  );

  proc document name=excel ;
    replay \Table_3#1\Caveats#1;
  run;

ODS Excel close;

[PhilC]

So if I change

title;footnote;*<---   this title should work;

to

title "Table 3 Caveats";footnote;*<---   this title should work;

The title statement changes the title.  That makes it work for me

I did this yesterday!  Any way, I think I've shown you a true bug.  But please confirm this by running this code (large code above) to see if the title statement does or does not clear the last title.  Then I'll know if it is my computer or its a true bug.  I think.

[PhilC]

I submitted a SAS Technical Support Request.

Only the first of line of my intended entry in “Diagnostic Steps” displays in my e-mailed response from the system.  I wonder if it was truncated.  For prosperity, I'll post it.  It should have read:

Part of story is documented in communities.sas.com - Article"ODS, ODSText, Titles"
 (https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-ODSText-Titles/td-p/758140).

This originally was code in Enterprise Guide.  It originally had a form where the four macro subroutines were defined in nodes and then played back inside the program node within a ODS Excel block.  The dysfunctionality showed it self then.  To eliminate macros as a cause, I switched to using ODS and PROC DOCUMENT.  The issue persisted.  To request help from Communities.SAS.com I wrote it without EG using SASHELP datasets and the issue still persisted.  

I've tried to recreate this using a smaller number of procedure blocks; when I did I failed to recreate the dysfunction.  Otherwise, when using this code, which mimics closely to how I am using SAS in my work, the dysfunction appears. I did not experiment to see what number or combination of procedures that cause the dysfunction to be recreated.

To remove the titles, I've tried using TITLE statement inside and outside of MACRO blocks, inside and outside of ODS and PROC DOCUMENT blocks.  I attempted to use ODS PROCTITLE=(ON |OFF), inside and out; no success either.  I tested this code on a SAS installation other than my own.

[Cynthia_sas]

Hi:
  Showing some code would be good. It's not clear whether you have anything else on the sheet with the ODS TEXT or not. The issue is caused by the fact that ODS destinations work with OUTPUT OBJECTS and ODS TEXT is just generating a text string that doesn't really count as an output object and if you place it between 2 procedures, the ODS TEXT string will frequently get "stuck" to the end of the first procedure and not to the beginning of the second procedure.
  If you had procedure output and you wanted to stick a title or string before the procedure output, using a TITLE statement is the best way to do that. Otherwise, you might want to look at PROC ODSTEXT, because it creates an output object that should be able to be placed on a separate sheet just like any other output object.
  If you don't want to share code and data in the forums, then you might want to work with Tech Support to see if they can duplicate your issue in the current version of SAS or the version that you're using.
Cynthia