topic Re: Tagsets.excelxp within a macro in SAS Procedures

Tagsets.excelxp within a macro

lbarwick — Fri, 22 May 2015 14:04:33 GMT

Morning,

I have a macro that reads in data from multiple directories, manipulates the data so that only certain fields/records are kept, and then saves the output in the work directory. I am using call execute to pass values from a source table to each of my macro variables in the macro (example source data below). From the output, I want to use tagsets.excelxp to create one Excel workbook for each value of &prot, and then within each &prot workbook have multiple sheets for each value of &table. So, from the example data, I would want one workbook for Prot=AA1 with 3 worksheets (111, 222, 333) and one workbook for Prot=AA2 with 3 worksheets (111, 222, 333).

I have tried to include my tagsets step at the bottom of the macro as indicated in bold below. However, I believe because I have multiple iterations of &prot within my source table (one for each unique value of &table), that the end result workbook is only keeping the worksheet with data from last unique value of &prot._&table (e.g. Workbook for prot=AA1, but only one worksheet for data for 333). What is best approach here for what I wish to accomplish?

%macro freetext (projid,prot,table,fieldlist);

%if &projid=XXX %then %do;

libname &prot "path\&prot";

data &prot._&table;

set &prot..&table;

by prot;

array fieldname (*) $ &fieldlist:;

do i=1 to dim(fieldname);

if fieldname ne '' then freetext=1;

end;

keep site prot patid &fieldlist; if freetext=1; run;

%end;

ods _all_ close;

ods tagsets.excelxp path="path\&prot\" file="freetext_&prot..xls";

options (orientation='Landscape'

style=statistical;

ods tagsets.ExcelXP

proc print data=&prot._&table noobs; options nocenter;

ods tagsets.excelxp options(sheet_name="&table"); run;

ods tagsets.excelxp close;

%mend freetext;

data _null_;

set source;

call

execute('%freetext('||projid||','||prot||','||table_name||','||fieldlist||')');

run;

PROJID	PROT	TABLE_NAME
AAA	AA1	111
AAA	AA1	222
AAA	AA1	333
AAA	AA2	111
AAA	AA2	222
AAA	AA2	333

Re: Tagsets.excelxp within a macro

RW9 — Fri, 22 May 2015 14:28:26 GMT

I find the macro quite hard to read. I can see you want 2 Excel files with three tabs, thats easy enough:

data _null_;

set source;

by prot;

if first.prot then call execute('ods tagsets.excelxp file="abc_'||strip(prot)||'.xml" options=(orientation="landscape");');

call execute('ods tagsets.excelxp options=(sheet_name='||strip(table_name)||');

proc print data=...;

run;');

if last.prot then call execute('ods tagsets.excelxp close;');

run;

This will create two files each with three sheets. Try to keep your code as simple and in blocks. I.e. do one thing to get an output. Then with that output move onto the next step. Once it works once, then re-assess it to see if it can be put into a macro.

Re: Tagsets.excelxp within a macro

PaigeMiller — Fri, 22 May 2015 14:30:37 GMT

You are invoking ODS TAGSETS.EXCELXP several times, and I'm not sure why you are doing that. You even have one with no semi-colon on the end and no options for the ODS TAGSETS.EXCELXP statement.

I think you want only one such ODS TAGSETS.EXCELXP statement in the macro, and one ODS TAGSETS.EXCELXP CLOSE;

Furthermore, the first time you run the macro, you specifiy both the file and sheet_name in one ODS TAGSETS.EXCELP statement. The next time your execute the macro, you'd want to make sure there is no file= but there is a sheet_name=. Finally, when all is done, all sheets have been written, you then issue the ODS TAGSETS.EXCELXP CLOSE; command.

EDIT: Looks like has offered the same solution, with some example code.

Re: Tagsets.excelxp within a macro

lbarwick — Fri, 22 May 2015 16:27:07 GMT

Thanks for feedback,and sorry about typos in macro - working remotely today and hard to toggle b/tw VPN session and this site.

I see what your example is trying to do, and I'm assuming this gets nested within my existing macro? My code isbelow but get an error. I had to modify the proc print data step so that it would read the data correctly from work directory, otherwise everything else is same as suggested.

8 !+last.prot then call execute('ods tagsets.excelxp close;'); run;

ERROR: Undeclared array referenced: execute.

ERROR 79-322: Expecting a ;.

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR 22-322: Syntax error, expecting one of the following: +, =.

ERROR 76-322: Syntax error, statement will be ignored.

proc sort data=source; by prot; run;

data _null_;

set source;

by prot;

if first.prot then call execute('ods tagsets.excelxp file=(abc_'||strip(prot)||'.xml)');

call execute('ods tagsets.excelxp options=(sheet_name='||strip(table_name)||');

proc print data=('||strip(prot)||'_'||strip(table_name)||'); run;')

if last.prot then call execute('ods tagsets.excelxp close;');

run;

Re: Tagsets.excelxp within a macro

lbarwick — Fri, 22 May 2015 18:28:03 GMT

Alright, sorry for additional reply but I got the output I wanted by putting the following code outside my macro. Two issues remain:

when I include '||strip(table_name)||' after sheet_name=, the log says it is expecting a quoted string. But - I can see from the log that the program is resolving sheet_name to the value of table_name. If I instead enclose ||strip(table_name)|| in double quotes, the program runs without error, but now all the sheet names in the output are the string literal "||strip(table_name)||". Any thoughts?
When the program runs without error, I get a warning:

WARNING: Data too long for column "XXX"; truncated to 91 characters to fit

How can I override the default length?

data _null_;

set source;

by prot;

if first.prot then call execute('ods tagsets.excelxp path="path\'||strip(prot)||'"

file="abc_'||strip(prot)||'.xml";');

call execute('ods tagsets.excelxp options(sheet_name="test");

proc print data='||strip(prot)||'_'||strip(table_name)||'; run;');

if last.prot then call execute('ods tagsets.excelxp close;');

run;

Re: Tagsets.excelxp within a macro

RW9 — Sat, 23 May 2015 10:09:08 GMT

Well point 1 is simple and my fault, the code was missing a bit:

call execute('ods tagsets.excelxp options=(sheet_name="'||strip(table_name)||'");

You will note after the sheet_name= there is a double quote ", then a single quote. The call execute accepts a string, hence it needs to be quoted - in my case I use single quotes for the complete string. Within that string we are generating, the sheet_name also requires a string, so that needs to be quoted as well, so I use double quotes.

What this should then say is:

add the text ods tagsets.excelxp options=(sheet_name="

to the program stream.

Then the text from the variable table name

Then the text ");

That might resolve your second issue also however if table_name is longer than 91 you will have problems as Excel has limits on length.

Re: Tagsets.excelxp within a macro

lbarwick — Tue, 26 May 2015 12:49:02 GMT

Thanks, this worked! And I solved the truncation issue by adding 'ods listing close' before the data step that prints the reports.