Help using Base SAS procedures

Export to an ".xls" excel file

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Export to an ".xls" excel file

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

;


Accepted Solutions
Solution
‎09-16-2014 10:46 AM
Trusted Advisor
Posts: 3,214

Re: Export to an ".xls" excel file

Posted in reply to wutao9999

All Replies
Solution
‎09-16-2014 10:46 AM
Trusted Advisor
Posts: 3,214

Re: Export to an ".xls" excel file

Posted in reply to wutao9999

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

---->-- ja karman --<-----
Regular Contributor
Posts: 233

Re: Export to an ".xls" excel file

Posted in reply to wutao9999
Regular Contributor
Posts: 233

Re: Export to an ".xls" excel file

Posted in reply to wutao9999

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;

New Contributor
Posts: 3

Re: Export to an ".xls" excel file

Posted in reply to wutao9999

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;

Regular Contributor
Posts: 233

Re: Export to an ".xls" excel file

PROC Star
Posts: 7,485

Re: Export to an ".xls" excel file

Posted in reply to wutao9999

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 )

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 266 views
  • 6 likes
  • 5 in conversation