How do i Export Multiple Sas Datasets into ms excel workbook wiht multiple sheets ?

[vivekvardhan]

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

[RW9]

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;

[vivekvardhan]

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;

[RW9]

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.

[Tom · Accepted solution]

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 ;

[vivekvardhan]

Hi Tom,

 Thanks for your suggestion

the code is working fine but I'm getting popup while opening the excel book.

[Tom]

The file is actually an XML file. If you try to trick Windows into thinking it is an XLS or XLSX file by naming the file with one of those extensions then Excel will normally warn you about that when you try to open it.

[Ksharp]

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;

[vivekvardhan]

SAS 9.3
I tried to use libname method.

[Ksharp]

So DBMS=XLSX is not available to you . Try DBMS=EXCEL or XLS .