"title;" statement not clearing out titles

[mmraja516]

Hi there,

I'm having a problem where a title from one sheet in an Excel file I'm creating does not clear even with a "title;" statement after the PROC. 

I have the titles from this sheet of "Preview of DM Dataset" and "List of Variables" appearing on the next sheet despite the "title;" statement after the proc sql step. I played around a bit and added another simple proc print after the "title;" and the titles appeared before that proc but not on the next sheet, so it seems the titles are "lingering one proc too long" for some reason. I have multiple other sheets being created in this excel file, but this proc sql step is the only one giving me this issue (it appears in another sheet as well laid out exactly the same just with a different dataset). 

I've noticed this only happens in the excel output, but not in the output in the SAS results window. Is there something more I have to do with excel and proc sql or something?

Does anyone have any idea how to fix this? I've tried so many things!! Please help!

/**************************************************************************************************************************************************************************/
ods excel options(sheet_interval="now" autofilter='all' sheet_name='Demographic Variable List' row_heights='0, 0, 0, 12, 0, 0, 0' title_footnote_width="14" FLOW='Tables');

title justify=left 'Preview of DM Dataset';
title2 justify=left 'List of variables';
proc sql;
	select varnum, name, label, type, length, format
	from dictionary.columns
	where libname = upcase("X") and memname = upcase("dmdata")
	order by 1;
quit;
title;
/* If I add a simple proc print or anything here, both titles still show here but then not on the next sheet like they do now */

/**************************************************************************************************************************************************************************/
ods excel options(sheet_interval="now" autofilter='all' sheet_name='Preview Demographic Data' row_heights='0, 0, 0, 12, 0, 0, 0' title_footnote_width="14" FLOW='Tables');
%macro previewdmdata;
%if &extension_dm. = xpt or &extension_dm. = XPT %then %do;
	title;
	%if &nobs_varnames_dmdata_sq. > 0 /* code continues in macro with conditional logic */

[Reeza]

What version of SAS do you have?
You can test it using:

proc product_status;run;

Results will be in the log. Should be something like SAS 9.4TS1M5

[ballardw]

Several things are missing to have a chance of answering:

Where is the macro variable &extension_dm. set?

Are 100 percent sure that it never has values like Xpt? You aren't checking for that. Better would be to make a case insensitive comparison like 

%if %upcase(&extension_dm.) = XPT %then %do;

That will take care of xpt, XPT, Xpt,XpT, xpT, XPt, XPt etc.

Second: Show the entire macro a little snippet shown could mean that the actual problem is elsewhere.

Third, Show the actual to the macro.

Did you run your macro with the options MPRINT and SYMBOLGEN set? And if you have enough complicate logic MLOGIC?

[Quentin]

I'm having a hard time trying to understand what results your are getting.

I would suggest trying to make a small example, without macro code, that illustrates the problem.

For example, if I submit this code:

ods excel file="Q:\junk\myfile.xlsx" options(sheet_interval="now" autofilter='all' sheet_name='Demographic Variable List');

title1 "hi mom" ;
proc sql;
	select name
	from sashelp.class
  ;
quit;
title;

proc print data=sashelp.shoes(obs=5) ;
run ;

ods excel close ;

I get what I would expect.  The Excel file has two sheets, the first sheet has a title (by default the title shows up in the header, you don't see it in the cells of the worksheet), the second sheet does not have a title.

Can you share the code for an example like that, which we can run, that is giving you surprising results?

Macros can be tricky, which is why I suggest starting with non-macro code.  Once you have non-macro code that works like you want, that usually makes it easier to debug macro code. If you don't have working non-macro code, it can be hard to know where there is a mistake in your macro code or your SAS code.

[mmraja516]

Thank you all for your responses, but I have resolved the issue.

[ballardw]

---

@mmraja516 wrote:

Thank you all for your responses, but I have resolved the issue.

---

Please help anyone else that may find this thread looking for a similar issue and describe exactly how you "resolved the issue". Then mark that post with the details as the solution.