topic Export multiple datasets into excel with different sheets in SAS Enterprise Guide

Export multiple datasets into excel with different sheets

Ravikumarkummari — Thu, 17 Aug 2017 06:34:53 GMT

Hi all,

I am using sas enterprise guide 5.1 version with MS Office-2010.

I want to export two datasets into excel with 2 different sheets, I tried proc export

proc export data=dataset1 outfile="filename.xlsx"
dbms=xlsx

replace;
sheet="salaries";
run;

proc export data=dataset2 outfile="filename.xlsx"
dbms=xlsx

replace;
sheet="department";
run;

After this i am keeping this file in zip file and sending automailer.

ods package(Accounts) open nopf;
ods package(Accounts) add file="filaname.xls";
ods package(Accounts) publish archive
properties(archive_name="Accounts.zip"
archive_path="/SAS/BIU/INPUT");
ods package(Accounts)
close;

But in mail only the second sheets appears, is there any solution of exporting two sheets in excel.

In this dbms=excel and excelcs are not working.

Any help would be greatly appreciated

Re: Export multiple datasets into excel with different sheets

Ksharp — Thu, 17 Aug 2017 12:35:03 GMT

Get rid of 'replace' ?

Re: Export multiple datasets into excel with different sheets

Reeza — Thu, 17 Aug 2017 15:12:51 GMT

I don't believe DBMS=XLSX supported multisheet excel workbooks until the latter versions. You need to include your SAS version, not EG.

Re: Export multiple datasets into excel with different sheets

Ksharp — Fri, 18 Aug 2017 12:52:58 GMT

As @Rezza pointed out, try other engine.
DBMS=excel 
DBMS=xls