topic Re: Create multiple tabs in one workbook in SAS Enterprise Guide

Create multiple tabs in one workbook

Belle — Fri, 06 Mar 2015 00:55:40 GMT

Hi members,

I am running SAS EG4.3 on the server, and trying to create an excel file with multiple tab in it, and then send this file to myself; however, I keep getting an error saying "ERROR: DBMS type XLSX not valid for export.", I did some research, but tried several suggestions online, but it still doesn't work.

Do I need to create an Excel file before I attach in the mail?

Please help.

Thank you!

/*** output an Excel with multiple tab ***/

data &server..test2;

set &server..test;

run;

proc export data = &server..test

outfile = "c:/sas/test"

dbms = excel replace;

sheet = check1;

run;

proc export data = &server..test2

outfile = "c:/sas/test"

dbms = excel replace;

sheet = check2;

run;

ERROR: DBMS type EXCEL not valid for export.

/*** sent an email ***/

data _null_;

file sendit email

to='email.com'

subject='Demo Results'

attach=('&server..test2.xls'

content_type="application/excel"

);

RUN;

ERROR: Error opening attachment file test2.xls.

ERROR: Physical file does not exist, server..test2.xls.

Please let me know if you need more information. Thanks

Re: Create multiple tabs in one workbook

Reeza — Fri, 06 Mar 2015 03:23:19 GMT

Have you tried explicitly writing your file name in the outfile?

EG 4.3 probably cant create XLSX file as it was too old at the time.

c:\temp\test.xls

Re: Create multiple tabs in one workbook

ChrisHemedinger — Fri, 06 Mar 2015 13:16:59 GMT

For DBMS=EXCEL to work, you need to have SAS/ACCESS to PC Files licensed and installed in your SAS environment. It appears that you don't have that.

The alternative for SAS 9.2 is to use ODS tagsets.ExcelXP, which creates an Excel-compatible XML file. For SAS 9.4 users, you can use ODS EXCEL to create an XLSX file directly. Both of these methods work in Base SAS.

You can also export to CSV and attach that in your e-mail. It's a simple file format that opens in Excel, but you won't have the control over multiple sheets that you're looking for. You would have to export multiple CSV files.

Chris

Re: Create multiple tabs in one workbook

Belle — Sat, 07 Mar 2015 06:13:42 GMT

Thanks Reeza and Chris. It seems like this is very complicated.

I think I'm going to find a workaround and export as csv / xslx files as part of the process flow.

btw...my final goal is to fit the final report template, I can use EED to read in.

Re: Create multiple tabs in one workbook

Ksharp — Mon, 09 Mar 2015 06:59:36 GMT

Libname statement is a good choice .


libname x excel 'c:\temp\multi.xls';

data a_20150101 a_20150204 a_20150903;
   set sashelp.class;
   run;

proc copy in=work out=x memtype=data ;run;

libname x clear;

Xia Keshan