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 |
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.
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.
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;
Alright, sorry for additional reply but I got the output I wanted by putting the following code outside my macro. Two issues remain:
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;
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.
Thanks, this worked! And I solved the truncation issue by adding 'ods listing close' before the data step that prints the reports.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: