Hi all,
I have seen many posts and answers for my query, those are not helping me.
I'm using remote sas server, sas 9.3 version. all datasets similar variables.
here is my query for an example :
have 20 datasets.
I want to export all datasets into one excel workbook with 20 sheets.
when I use dbms=xlsx below code last 20th dataset only comes in output.
&&_table&i. it contains all the 20 datasets names.
cnt has value of 20
here is my peice of code :
%do i = 1 %to &cnt;
PROC EXPORT DATA= &&_table&i.
outfile= "pathwhereiwant to store.xlsx"
dbms=XLSX replace;
sheet="&&_table&i.";
run;
%end;
i have used libname method not working
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.
expecting resolution from experts.
Thank you
You need to move the open/close outside of the %DO loop.
ods listing close;
ods tagsets.ExcelXP file='full name of excel file' ;
%do i = 1 %to &cnt;
ods tagsets.ExcelXP OPTIONS (SHEET_NAME = "&&_table&i.");
proc report data=general_data nowd;
where Domain_Prefix = "&&_table&i." ;
columns _all_;
run;
%end;
ods tagsets.ExcelXP close;
ods listing ;
You are using SAS 9.3 so more recent functions like native XLSX does not work for you - a very good reason to update your software to something modern.
You could do it in export - me I would prefer tagsets.excelxp as this gives you a lot more control and formatting over the output. The surrounding code is the same in either event - note that <youlib> should be replaced with the library name you have your data in in uppercase.
data _null_; set sashelp.vtable (where=(libname="<yourlib>")); call execute(cats('proc export data=<yourlib>.',memname,' outfile="pathwhereiwant to store/file.xlsx"; sheet="',memname,'";run;')); run;
That will create a proc export for each dataset in the given lib. Ods is similar:
ods tagsets.excelxp file="pathwhereiwant to store/file.xml"; data _null_; set sashelp.vtable (where=(libname="<yourlib>")); call execute(cats('ods tagsets.excelxp options(sheet_name="',memname,'"); proc report data=<yourlib>.',memname,' nowd; columns _all_; run;')); run; ods tagsets.excelxp close;
ods TAGSET ALSO NOT WORKING
/* into multiple datasets */
%do i = 2 %to &cnt;
%put count =&i. tablename= &&_table&i.;
data &&_table&i.;
set general data_(where=(Domain_Prefix = "&&_table&i." ));
run;
ods listing close;
ods tagsets.ExcelXP
path ='path where i want to save the excel sheet'
file='newsdtmspec.xls' OPTIONS (SHEET_NAME = "&&_table&i.");
proc report data= &&_table&i. nowd; columns _all_; run;
%end;
ods tagsets.ExcelXP close;
ods listing;
This: "ods TAGSET ALSO NOT WORKING" - does not tell me anything, even if you SHOUT IT.
Also, please review the code I have provided you, it is not code to try to put into code you already have, it is code in and of itself, you do not need %do's or && nonsense.
You need to move the open/close outside of the %DO loop.
ods listing close;
ods tagsets.ExcelXP file='full name of excel file' ;
%do i = 1 %to &cnt;
ods tagsets.ExcelXP OPTIONS (SHEET_NAME = "&&_table&i.");
proc report data=general_data nowd;
where Domain_Prefix = "&&_table&i." ;
columns _all_;
run;
%end;
ods tagsets.ExcelXP close;
ods listing ;
Hi Tom,
Thanks for your suggestion
the code is working fine but I'm getting popup while opening the excel book.
What is your SAS version ?
libname x xls 'c:\xxxx.xls';
libname x excel 'c:\xxxx.xls';
proc copy in=work out=x;
select a b c d;
run;
So DBMS=XLSX is not available to you . Try DBMS=EXCEL or XLS .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.