topic Re: Proc Export to Excel - Multiple tables to one xlsx file on different sheets in ODS and Base Reporting

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

Dd07 — Thu, 21 Jul 2016 12:12:45 GMT

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: .

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

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

RW9 — Thu, 21 Jul 2016 12:21:34 GMT

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.

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

Reeza — Thu, 21 Jul 2016 14:09:11 GMT

Tagsets should work. What was your issue with it?

Here's an example of how that works.

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

Dd07 — Thu, 21 Jul 2016 14:16:24 GMT

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.

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

Reeza — Thu, 21 Jul 2016 14:19:14 GMT

SAS 9.3 and XLSX does not support multiple sheets.

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

Dd07 — Thu, 21 Jul 2016 14:25:56 GMT

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;

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

RW9 — Thu, 21 Jul 2016 14:40:46 GMT

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.

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

PreenaKaur — Mon, 25 Jul 2016 09:51:21 GMT

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