Export multiple data set from SAS in Excel

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Export multiple data set from SAS in Excel


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


Accepted Solutions
Solution
‎01-23-2015 03:06 PM
Super User
Posts: 17,758

Re: Export multiple data set from SAS in Excel

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

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);

View solution in original post


All Replies
Trusted Advisor
Posts: 1,610

Re: Export multiple data set from SAS in Excel

I never tried an export where

sheet=" ";

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

Contributor
Posts: 46

Re: Export multiple data set from SAS in Excel

it's still showing the same error message.

Super User
Posts: 17,758

Re: Export multiple data set from SAS in Excel

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

Contributor
Posts: 46

Re: Export multiple data set from SAS in Excel

Excel 2010 and SAS 9.3

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

Solution
‎01-23-2015 03:06 PM
Super User
Posts: 17,758

Re: Export multiple data set from SAS in Excel

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

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);

Contributor
Posts: 46

Re: Export multiple data set from SAS in Excel


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!

Super User
Posts: 17,758

Re: Export multiple data set from SAS in Excel

Did you try DBMS=EXCEL as well?

Contributor
Posts: 46

Re: Export multiple data set from SAS in Excel

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

Super User
Posts: 10,471

Re: Export multiple data set from SAS in Excel

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)

Contributor
Posts: 46

Re: Export multiple data set from SAS in Excel

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?

Valued Guide
Posts: 3,208

Re: Export multiple data set from SAS in Excel

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.

---->-- ja karman --<-----
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 480 views
  • 4 likes
  • 5 in conversation