The SAS Output Delivery System and reporting techniques

Use of macro variables and options with tagset ExcelXP

Reply
Contributor
Posts: 53

Use of macro variables and options with tagset ExcelXP

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.
SAS Super FREQ
Posts: 8,743

Re: Use of macro variables and options with tagset ExcelXP

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]
Contributor
Posts: 53

Re: Use of macro variables and options with tagset ExcelXP

Thanks, Cynthia. I will give that a try. I assume this should also work with a macro variable in the form &&DSN&I ?
SAS Super FREQ
Posts: 8,743

Re: Use of macro variables and options with tagset ExcelXP

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]
Ask a Question
Discussion stats
  • 3 replies
  • 160 views
  • 0 likes
  • 2 in conversation