The SAS Output Delivery System and reporting techniques

Macro variable in title of proc print in ODS Excel disappears

Reply
Occasional Contributor
Posts: 5

Macro variable in title of proc print in ODS Excel disappears

I am trying to use ODS Excel to print a dataset to Excel.  I need to put a range of dates in the title.  The macro variable in the title disappears. 

 

I'm using SAS 9.4 (TS1M1)

 

Here is my code:

ods excel file="&OUTPUT_PATH.\&CONS_FILE._&yyyymmdd..xlsx"
options ( /* Display options */
	sheet_name="CONSUMER_RPT"
	sheet_interval='NONE'
	absolute_column_width='40,6,7'
	embedded_titles='on')
	;

run;

proc print data=cons_rpt noobs  sumlabel; * label;
	var 
		State      
		CT
 		; 
	by MarketArea;
	id MarketArea;
	label MarketArea='Grand Total'

	;
	sum ct;
	format ct comma10.;
	title "My Company Report";
	title2 "New Account by Market Area";
	title3 "Created &sysdate.";
/*	title3 "data for &STRDT3. through &ENDDT3.";*/

run;
ods excel close;

 

Using &sysdate will work, but the &STRDT3 and &ENTDT3 variables that I created do not show up.  I can print them to the sas log.

 

Can anyone tell me how to get user defined variables into the titles in a proc print in ODS Excel?

Super User
Posts: 11,343

Re: Macro variable in title of proc print in ODS Excel disappears

Posted in reply to lisagsmi1

You should show how you created those other variables. If you are making them in a separate macro then the values may currently only be local and not exist after the macro terminates.

 

I use macro variables like this frequently in my work and as long as the macro variable has the correct scope and generates valid text I have no problems in title statements.

Occasional Contributor
Posts: 5

Re: Macro variable in title of proc print in ODS Excel disappears

Hi, sorry, I forgot to add, I put this line at the top of my code.  Will that make the macros available to the ODS Excel Print?

 

%global num_rpt_obs num_corp_obs strdt3 enddt3;

 

Thanks.

 

Super User
Posts: 11,343

Re: Macro variable in title of proc print in ODS Excel disappears

Posted in reply to lisagsmi1

If that %global statement comes after the variables were created then possibly not. That status is only going to be global after the code is run.

Example:

 

%mymacro; /* creates the values in this macro but are not declared %global*/

 

%global myvar;

 

<attempt to use &myvar> Fails because the macro was not designated as global before the value was assigned. HOWEVER the fun part with debugging macro is that the second time it MAY work because the status set in the first code run that didn't quit work.

Best would be

%macro Mymacro;

   %global ThisVar thatvar anothervar;

....

%mend;

That way when you review your code 9 months later you see that it is supposed to make a global variable and is not dependant on an external %global statement.

Explicit statements for %local will also keep you from accidentaly changing a global variable.

 

Super User
Posts: 19,822

Re: Macro variable in title of proc print in ODS Excel disappears

Posted in reply to lisagsmi1

lisagsmi1 wrote:

 

Using &sysdate will work, but the &STRDT3 and &ENTDT3 variables that I created do not show up.  I can print them to the sas log.

 

Can anyone tell me how to get user defined variables into the titles in a proc print in ODS Excel?


 

If other macro variables work, you either

1. didn't create them properly,

2. created them as local macro variables that don't exist anymore,

3. aren't referencing them properly. 

 

Solutions:

1. We can't answer because you didn't show that

2. See #1

3. See #1

 

 

PROC Star
Posts: 1,322

Re: Macro variable in title of proc print in ODS Excel disappears

Posted in reply to lisagsmi1

You said the macro variables don't show up in the title.

 

If when you code:

title3 "data for &STRDT3. through &ENDDT3.";

You get a title "data for  through " that would suggest the macro variables STRDT3 and ENDDT3 exist but have no values (they are null). 

 

If you get a title "data for &STRDT3. through &ENDDT3." and a message in the log about the macro variables not resolving, that would indicate the macro variables do not exist.

 

The easiest way to see what macro variables exist is to add %PUT _USER_ ;  That will dump all the user-created macro variables to the log. 

Occasional Contributor
Posts: 5

Re: Macro variable in title of proc print in ODS Excel disappears

Posted in reply to lisagsmi1

Even though I put the %global statement at the top of my code, and did %put statements, the %put statements near where I needed to use my variables always showed them as blank. I obviously had some kind of weird scope problem going on. I solved this problem by simply defining the variables right before I needed to use them.

 

Thanks to all of you for your kind assistance and quick replies.

Ask a Question
Discussion stats
  • 6 replies
  • 282 views
  • 1 like
  • 4 in conversation