DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

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


Accepted Solutions
Solution
‎01-15-2018 11:51 PM
Super User
Super User
Posts: 8,129

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

Posted in reply to vivekvardhan

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 ;

View solution in original post


All Replies
Super User
Super User
Posts: 9,617

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

Posted in reply to vivekvardhan

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;
New Contributor
Posts: 4

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

 

 

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;

Super User
Super User
Posts: 9,617

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

Posted in reply to vivekvardhan

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.

Solution
‎01-15-2018 11:51 PM
Super User
Super User
Posts: 8,129

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

Posted in reply to vivekvardhan

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 ;
New Contributor
Posts: 4

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

 

 

Hi Tom,

 Thanks for your suggestion

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

exceliss.PNG

Super User
Super User
Posts: 8,129

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

Posted in reply to vivekvardhan
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.
Super User
Posts: 10,788

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

Posted in reply to vivekvardhan

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;

New Contributor
Posts: 4

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

SAS 9.3
I tried to use libname method.
Super User
Posts: 10,788

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

Posted in reply to vivekvardhan

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 276 views
  • 1 like
  • 4 in conversation