BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vivekvardhan
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

 

 

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User Tom
Super User

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
Calcite | Level 5

 

 

Hi Tom,

 Thanks for your suggestion

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

exceliss.PNG

Tom
Super User Tom
Super User
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
Super User

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
Calcite | Level 5
SAS 9.3
I tried to use libname method.
Ksharp
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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