BookmarkSubscribeRSS Feed
frisco
Calcite | Level 5
Hi, I would like to use a macro variable for the option "sheet_name". Is there a way to do this in 9.1.3 without modifying the tagset itself? TIA.
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
Hi:
This used to work for me in SAS 9.1.3, but I'm not sure whether it worked out of the box or whether I had to download a 9.1.3 updated tagset template.

cynthia

[pre]
ods tagsets.excelxp file='c:\temp\my_wb.xls'
style=sasweb;
%let override_sheetname='Wombat';
proc print data=sashelp.class;
run;

%let override_sheetname='Koala';
proc freq data=sashelp.shoes;
tables Region;
run;
ods tagsets.excelxp close;
%let override_sheetname=;
[/pre]

This syntax, also works. So if all you want to do is use a macro variable in the OPTIONS list, you should get the same end result.

[pre]
%let sh1 = Wombat;
%let sh2 = Koala;
ods tagsets.excelxp file='c:\temp\my_wb2.xls'
style=sasweb options(sheet_name="&sh1");

proc print data=sashelp.class;
run;

ods tagsets.excelxp options(sheet_name="&sh2");
proc freq data=sashelp.shoes;
tables Region;
run;
ods tagsets.excelxp close;

[/pre]
frisco
Calcite | Level 5
Thanks, Cynthia. I will give that a try. I assume this should also work with a macro variable in the form &&DSN&I ?
Cynthia_sas
SAS Super FREQ
Hi:
By the time the code goes to the compiler, TAGSETS.EXCELXP doesn't even see the macro variable reference. Any code that contains a macro "trigger", such as & or % goes into a "macro word scanner" and resolver phase prior to compilation. This is where macro variable references are turned into text.

There's nothing special about macros and TAGSETS.EXCELXP -- it works like the whole rest of SAS works as far as macro variables. The same rules apply. If you want your macro variable to be treated as a text string in the code, and if in your usage context, the text string needs quotes, then you must use double quotes in order to allow the macro word scanner and resolver to do its work.

As for whether &&DSN&I will work, that depends on what your desired end result is. It looks like you possibly have a macro %DO loop. There are several rules that pertain to "indirect reference" of macro variables -- this is what you are doing when you use '&&' or '&&&' in a macro variable reference. (as explained here)
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a001071915.htm

You can test the correct reference without a lot of fuss as shown below -- then you decide which form of indirect reference is correct, based on whether you want the final resolved value to be FRED, SNAME1 or even DSN1.

cynthia

[pre]
%let sname1 = fred;
%let dsn = sname;
%let i = 1;

%put *** sname1 = &sname1 dsn= &dsn i= &i;
%put try 1: &&dsn&i;
%put try 2: &dsn&&i;
%put try 3: &&&dsn&i;
%put try 4: dsn&i;
[/pre]

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
  • 3 replies
  • 786 views
  • 0 likes
  • 2 in conversation