BookmarkSubscribeRSS Feed
Dd07
Fluorite | Level 6

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

 

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

 

Dd07
Fluorite | Level 6

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.

Reeza
Super User

SAS 9.3 and XLSX does not support multiple sheets. 

Reeza
Super User

Tagsets should work. What was your issue with it?

 

Here's an example of how that works. 

Dd07
Fluorite | Level 6

 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;

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

PreenaKaur
Calcite | Level 5

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

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 7 replies
  • 9282 views
  • 3 likes
  • 4 in conversation