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";
;
https://communities.sas.com/thread/61115
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;
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;
Davide,
There is a hotfix for what you are looking for
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 )
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.