proc export to excel not replacing sheet

[GeorgeBonanza]

Hello,

I am using the below code to export a sas dataset to an Excel worksheet.  If the workbook doesn't exist then it is created along with the sheet and everything works as intended.  However, if I change the work.HAVE dataset and re-run the export the SAS log tells me that "'WANT' range/sheet was successfully created" but the data in the sheet doesn't actually change and neither does the modified date on the Excel file.  I am working in SAS Enterprise Guide 7.15 and Excel 2016.  Any insight is greatly appreciated.  Thanks in advance.

proc export data= work.HAVE

outfile = "&path\test.xlsx"

dbms= EXCEL replace;

sheet= "WANT";

run;

[SASKiwi · Accepted solution]

Suggest you try DBMS = XLSX since that is the actual workbook type.

[Kurt_Bremser]

You use Enterprise Guide, so my first guess is that you are manipulating an Excel file on the SAS server, but looking at a file on your desktop.

[GeorgeBonanza]

Yes, I am using EG but the job is running on the local machine.

[andreas_lds]

Which SAS version is in use? Are latest updates installed?

[GeorgeBonanza]

I am using Enterprise Guide 7.1 and to the best of my knowledge updates are current.

[andreas_lds]

I asked which version of the sas-software is used: use

%put &=Sysvlong;

to write version number to the log.

[GeorgeBonanza]

I misunderstood your question. Apologies. The version is
SYSVLONG=9.04.01M7P080520

[andreas_lds]

---

@GeorgeBonanza wrote:
I misunderstood your question. Apologies. The version is
SYSVLONG=9.04.01M7P080520

---

The latest release, so i don't think that the sas version is the culprit. The following steps work as intended:

data narf;
   set sashelp.class;
run;

proc export data=work.narf dbms=xlsx outfile="&Benutzer\data\narf.xlsx" replace;
   sheet= 'narf';
run;


data narf;
   set sashelp.class;
   Age = Age + 10;
run;

proc export data=work.narf dbms=xlsx outfile="&Benutzer\data\narf.xlsx" replace;
   sheet= 'narf';
run;

So have you tried changing dbms to "xlsx" as recommended by @SASKiwi ?

[GeorgeBonanza]

I tried running the code you shared and it does work as intended to create the file on the first export. However, after changing the age and rerunning the export step it does overwrite the existing "narf" sheet but it also creates another file called "narf.xlsx.bak". I tried exporting to both the desktop and shared folder on a LAN and got the same results. I haven't seen this before as it didn't happen using dbms = EXCEL

[Sajid01]

Proc export does create a backup of existing xlsx workbook before over writing it. This is the normal behavior.
It does this both on Windows and Linux.
If you do not want this back this can be deleted or by running a macro to delete it. This forum post has the details  https://communities.sas.com/t5/SAS-Programming/Removing-the-bak-with-proc-export/td-p/787216