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

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

vivekvardhan — Thu, 11 Jan 2018 13:21:58 GMT

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

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

RW9 — Thu, 11 Jan 2018 14:18:56 GMT

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;

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

vivekvardhan — Fri, 12 Jan 2018 07:34:13 GMT

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;

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

RW9 — Fri, 12 Jan 2018 08:54:18 GMT

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.

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

Ksharp — Fri, 12 Jan 2018 14:02:10 GMT

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;

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

vivekvardhan — Fri, 12 Jan 2018 15:12:35 GMT

SAS 9.3
I tried to use libname method.

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

Ksharp — Sat, 13 Jan 2018 10:10:53 GMT

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

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

Tom — Sat, 13 Jan 2018 15:29:18 GMT

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 ;

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

vivekvardhan — Tue, 16 Jan 2018 04:51:00 GMT

Hi Tom,

Thanks for your suggestion

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

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

Tom — Tue, 16 Jan 2018 05:03:35 GMT

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.