topic Re: Export multiple data set from SAS in Excel in SAS Programming

Export multiple data set from SAS in Excel

Armand — Fri, 23 Jan 2015 17:14:41 GMT

I have used the code below to export 3 data set in excel:

%macro export(dst=);

proc export data=mywork.&dst

outfile="h:\My Documents\MyFile\SAS\MyWork\Result.XLS"

Label dbms=xls Replace;

sheet=" ";

run;

%mend;

%export(dst=Comparison);

%export(dst=Test1);

%export(dst=Test2);

The log summarize successfull but i am unable to open the file Result.xls from my directory. When i try to open it : "Excel found unreadable content in Result.xls.Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes." I clicked yes but it does not open.

Any idea?

Thank you for your help

Re: Export multiple data set from SAS in Excel

PaigeMiller — Fri, 23 Jan 2015 17:51:20 GMT

I never tried an export where

sheet=" ";

What happens if you actually give a sheet name or comment out this line?

Re: Export multiple data set from SAS in Excel

Armand — Fri, 23 Jan 2015 18:36:04 GMT

it's still showing the same error message.

Re: Export multiple data set from SAS in Excel

Reeza — Fri, 23 Jan 2015 18:49:57 GMT

How big is your dataset? What version of Excel and SAS are you on?

Re: Export multiple data set from SAS in Excel

ballardw — Fri, 23 Jan 2015 18:54:36 GMT

How many variables in any of those set?

How many observations?

If you set DBMS=CSV do you get the same problem?(don't use sheet option with csv)

Re: Export multiple data set from SAS in Excel

Armand — Fri, 23 Jan 2015 19:06:14 GMT

Excel 2010 and SAS 9.3

The first data set 36440 (6 variables) rows and the two remaining data set are 1 row

Re: Export multiple data set from SAS in Excel

Armand — Fri, 23 Jan 2015 19:14:03 GMT

the csv works but i am getting only the last data set (Test2). And one i take out the "Replace", only the first data set is exported.

How to get all of them in one workbook?

Re: Export multiple data set from SAS in Excel

Reeza — Fri, 23 Jan 2015 20:06:34 GMT

You can apply a hot fix and use XLSX engine.

51580 - The XLSX engine is enhanced to write multiple sheets per Microsoft Excel file in the first maintenance release of SAS® 9.3 (TS1M1)

Or try changing DBMS to Excel or ExcelCS. You may want to change your sheet names as well, to a dynamic variable otherwise you'll always overwrite the sheet.

%macro export(dst=);

proc export data=mywork.&dst

outfile="h:\My Documents\MyFile\SAS\MyWork\Result.XLSX"

Label dbms=ExcelCS Replace;

sheet="&dst";

run;

%mend;

%export(dst=Comparison);

%export(dst=Test1);

%export(dst=Test2);

Re: Export multiple data set from SAS in Excel

Armand — Fri, 23 Jan 2015 20:47:03 GMT

I could not apply the hot fix. So i tried the last option and i go this error message: Failed to connect to the Server

Is there a way we work around the idea in Post 6? thanks!

Re: Export multiple data set from SAS in Excel

Reeza — Fri, 23 Jan 2015 20:52:56 GMT

Did you try DBMS=EXCEL as well?

Re: Export multiple data set from SAS in Excel

jakarman — Fri, 23 Jan 2015 21:01:12 GMT

Did you ever used the LIBNAME Excel approach? SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

Sheets are the same as the SAS-datasets.

Re: Export multiple data set from SAS in Excel

Armand — Fri, 23 Jan 2015 21:24:14 GMT

Sorry, long day. it did work with Excel. Thanks a lot.