Export to an ".xls" excel file

[wutao9999]

I want to export a data set to "sheet1" in an excel file, if the data already exists in "sheet1", then replace it.  The below SAS code works well if the excel file is a ".xlsx" file.  However, I need do the similar work for a ".xls" excel file, can I do such thing for a ".xls" excel file?  Thank you.

EXPORT DATA=data1

OUTFILE="c:\test.xlsx"

DBMS=EXCELCS REPLACE;

SHEET="sheet1";

;

[jakarman · Accepted solution]

https://communities.sas.com/thread/61115

[Hima]

20923 - How REPLACE option works with PROC EXPORT and Excel files in SAS 9.1

[Hima]

Just an FYI, I tried and working fine for me when used replace option. Here is the code.

proc export data=want

outfile='C:\want.xls'

dbms=excel replace;

run;

[davide]

I think that wutao9999 want to write in a specific sheet of an existing excel file (at lest is what i also need to do),

for what i read from Hima's link seems that SAS when want to write in a specific sheet try to create it , and if the name that we want to use is alredy in used SAS add a numeric character to avoid the duplication...

so, this mean that i have no way to export a data set in a specific sheet in a alredy existing excel file?

thank a lot

Davide

just to be sure i add the code that i'm using

proc export

data = test

outfile = "C:\test\fileexcel.xlsx"

DBMS = EXCEL

REPLACE;

SHEET = "foglio1";

run;

[Hima]

Davide,

There is a hotfix for what you are looking for

51580 - The XLSX engine is enhanced to write multiple sheets per Microsoft Excel file in the first m...

[art297]

wutao9999 and davide: If either or both of you are on a Windows' operating system, take a look at the free macro that ,  and I wrote:

A Poor/Rich SAS Users Proc Export - sasCommunity

It has three possible values for the type parameter: N to create a new workbook, A to add a worksheet and M to modify an existing worksheet. It also has a range parameter so that you can write data starting at a particular range without having to predefine the range in Excel.  The macro also provides a way to take advantage of Excel templates (see, e.g., http://www.torsas.ca/attachments/File/09122014/Art_TASS_12Sep2014.pdf )