BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Armand
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
PaigeMiller
Diamond | Level 26

I never tried an export where

sheet=" ";

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

--
Paige Miller
Armand
Calcite | Level 5

it's still showing the same error message.

Reeza
Super User

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

Armand
Calcite | Level 5

Excel 2010 and SAS 9.3

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

Reeza
Super User

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

Armand
Calcite | Level 5


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!

Reeza
Super User

Did you try DBMS=EXCEL as well?

Armand
Calcite | Level 5

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

ballardw
Super User

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)

Armand
Calcite | Level 5

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?

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 11 replies
  • 2268 views
  • 4 likes
  • 5 in conversation