topic Re: Export to an ".xls" excel file in SAS Procedures

Export to an ".xls" excel file

wutao9999 — Tue, 16 Sep 2014 14:41:34 GMT

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

;

Re: Export to an ".xls" excel file

jakarman — Tue, 16 Sep 2014 14:46:49 GMT

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

Re: Export to an ".xls" excel file

Hima — Tue, 16 Sep 2014 15:03:56 GMT

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

Re: Export to an ".xls" excel file

Hima — Tue, 16 Sep 2014 15:14:02 GMT

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;

Re: Export to an ".xls" excel file

davide — Tue, 16 Sep 2014 15:35:56 GMT

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;

Re: Export to an ".xls" excel file

Hima — Tue, 16 Sep 2014 15:45:51 GMT

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 maintenance release of SAS® 9.3 (TS1M1)

Re: Export to an ".xls" excel file

art297 — Tue, 16 Sep 2014 16:15:30 GMT

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 )