The SAS Output Delivery System and reporting techniques

Proc Export to Excel - Multiple tables to one xlsx file on different sheets

Reply
Contributor
Posts: 35

Proc Export to Excel - Multiple tables to one xlsx file on different sheets

Hello All,

 

I need asistance when i am trying to export multiple tables to one spreadhseet its keeping the last one Howver i tried with dbms=excelcs still did not work,tried with ods tagsets still the issue persist.

 

Please do  suggest.I am using sas 9.3

 

 

=======================================

 


97 %macro exp(dataset,path,date,Sheet);
98 proc export data=dd.&dataset.
99 outfile="&path.heatmap_&date..Xlsx"
100 DBMS=excelcs replace;
101 /*Need to check the excelcs dbms engine*/
102 Sheet="&sheet.";
103 run;
104 %mend exp;
105
106 %exp(Research_Items,&path.,&sysdate.,Research_Items);

ERROR: Failed to connect to the Server: .

 

===================================

Super User
Super User
Posts: 7,401

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

Well, the replace will create a new file each time, so that won't work.  DBMS should be xlsx.  Does it work without the macro code, i.e. just plain proc export?  You oculd be missing the office interface.

 

 

Contributor
Posts: 35

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

Yes .Its working fine with xlsx however its keeping only one dataset to workbook.

My requirement is need to have multiple sheet in a single workbook.

Super User
Posts: 17,823

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

SAS 9.3 and XLSX does not support multiple sheets. 

Super User
Posts: 17,823

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

Tagsets should work. What was your issue with it?

 

Here's an example of how that works. 

Contributor
Posts: 35

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

 i am using below code .

However it is keeping the last output in to the spreadsheet.

Please do suggest if we need to change anything or use some different approach.

filename myfile "xxxxxxxxxx.xml"

ods tagsets.excelxp body=myfile STYLE=HTMLBLUE OPTIONS(SHEET_NAME="CCCC");

proc print data=dd.CCCCnoobs;

run;

ods tagsets.excelxp body=myfile STYLE=HTMLBLUE OPTIONS(SHEET_NAME="DDDD");

proc print data=dd.dddd noobs;

run;

/*** close and clear ***/

ODS tagsets.excelxp CLOSE;

 

 

Super User
Super User
Posts: 7,401

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

Your mixing up HTML destination and XLSX destination.  To use XLSX:

ods tagsets.excelxp file="c:\xyz.xlsx" style=htmlblue options(sheet_name="First");
proc print data=sashelp.class;
run;
ods tagsets.excelxp options(sheet_name="Second");
proc print data=sashelp.cars;
run;
ods tagsets.excelxp close;

You will note that I specify the file only once, at the top.  If you specify it any more it will overwrite.

Occasional Contributor
Posts: 11

Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets

You can try from below sample, its working for me : 

 

proc sql;

CREATE TABLE t1 AS

select * from abc;

run;

 

proc sql;

CREATE TABLE t2 AS

select * from def;

run;

 

proc sql;

CREATE TABLE t3 AS

select * from ghi;

run;

 

proc sql;

CREATE TABLE t4 AS

select * from jkl;

run;

 

ods TAGSETS.EXCELXP body=outbox rs=none style=styles.HTMLBlue;

ods TAGSETS.EXCELXP text="test sample text";

 

     ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="abc");

     proc print data=t1 noobs;

     run;

     ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="def");

     proc print data=t2 noobs;

     run;

     ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="ghi");

     proc print data=t3 noobs;

     run;

     ods TAGSETS.EXCELXP rs=none OPTIONS(SHEET_NAME="jkl");

     proc print data=t4 noobs;

     run;

ods TAGSETS.EXCELXP close;

 

filename outbox email

           to='abc@abc.com'

           from='def@def.com'

           type='application/vnd.ms-excel'

          subject='Sample First Program';

 

Creating excel sheet with 4 different tabs

 

Ask a Question
Discussion stats
  • 7 replies
  • 3411 views
  • 3 likes
  • 4 in conversation