topic Re: proc export to excel not replacing sheet in SAS Programming

proc export to excel not replacing sheet

GeorgeBonanza — Wed, 23 Feb 2022 21:43:11 GMT

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;

Re: proc export to excel not replacing sheet

SASKiwi — Wed, 23 Feb 2022 23:09:35 GMT

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

Re: proc export to excel not replacing sheet

Kurt_Bremser — Thu, 24 Feb 2022 06:38:30 GMT

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.

Re: proc export to excel not replacing sheet

andreas_lds — Thu, 24 Feb 2022 07:42:04 GMT

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

Re: proc export to excel not replacing sheet

GeorgeBonanza — Thu, 24 Feb 2022 12:35:01 GMT

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

Re: proc export to excel not replacing sheet

GeorgeBonanza — Thu, 24 Feb 2022 12:38:59 GMT

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

Re: proc export to excel not replacing sheet

andreas_lds — Thu, 24 Feb 2022 13:17:05 GMT

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

%put &=Sysvlong;

to write version number to the log.

Re: proc export to excel not replacing sheet

GeorgeBonanza — Thu, 24 Feb 2022 16:19:20 GMT

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

Re: proc export to excel not replacing sheet

andreas_lds — Fri, 25 Feb 2022 06:34:59 GMT

@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 ?

Re: proc export to excel not replacing sheet

GeorgeBonanza — Mon, 28 Feb 2022 15:15:59 GMT

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

Re: proc export to excel not replacing sheet

Sajid01 — Mon, 28 Feb 2022 16:32:52 GMT

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