BookmarkSubscribeRSS Feed
lisagsmi1
Calcite | Level 5

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?

6 REPLIES 6
ballardw
Super User

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.

lisagsmi1
Calcite | Level 5

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.

 

ballardw
Super User

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.

 

Reeza
Super User

@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

 

 

Quentin
Super User

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. 

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
lisagsmi1
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2402 views
  • 1 like
  • 4 in conversation