BookmarkSubscribeRSS Feed
hoff_sas
Calcite | Level 5

Hey all together,

I need to code 4 PROC EXPORT statements in one program.

All of them referencing the same excel workbook and sheet.

The code is like:

1.a.) reading the Excel sheet into a SAS dataset

1.b.) calculating/merging something

1.c.) update the Excel sheet by using PROC EXPORT statement

2.) doing something sensible like caching data from another remote host....

3.a.) reading the Excel sheet into a SAS dataset

3.b.) calculating/merging something

3.c.) update the Excel sheet by using PROC EXPORT statement

...and so on....iterating 4 times

The Problem:

Even if, according to the log all seems fine.....SAS only executes the first PROC EXPORT statement correctly.

For the 2nd, 3rd and 4th PROC EXPORT the new observations are not exported to the excel sheet.

Code snipped:

/* import existing user */

proc import datafile="&workspace.\All-Users-&assigned_year._mail.xlsx"

out=smf.old_user

DBMS=excel2007

replace;

run;

* merge new user and old user into new user dataset */

Data smf.cur_user;

set smf.old_user smf.abteicpu;

where user_id like 'XX_____';

keep user_id Vorname Name Abteilung Mail EXTERNALMAIL Phone;

run;

/* eliminate multiple entries */

proc sort data=smf.cur_user nodupkey;

by user_id;

run;

/* export user including contact data to excel */

PROC EXPORT data=smf.cur_user

DBMS=excel2007

OUTFILE="&workspace.\All-Users-&assigned_year._mail.xlsx" REPLACE;

run;

Again.....this code runs 4 times.....before each execution SAS connects to another remote host to collect data (closing endrsubmit before continuing pc processing).....

....the excel sheet "cur_user" is only updated the first time the code runs.

Do you guys have any hints and tipps reagarding this strange problem!?

Environment:

- Windows 7 64bit

- SAS 9.2

- Excel 2007

greetings

14 REPLIES 14
AncaTilea
Pyrite | Level 9

Hi.

the one thing I would suggest is to specify the sheet you want updated: (in your case: cur_user);

I would modify the export to something like this:

PROC EXPORT data=smf.cur_user

DBMS=excel2007

OUTFILE="&workspace.\All-Users-&assigned_year._mail.xlsx" REPLACE;

SHEET = "cur_user";

run;

So now, every time you export your data set, the cur_user sheet will get updated.

Good luck!

Anca.

hoff_sas
Calcite | Level 5

Hi Anca,

thanks for your post.....

Unfortunately I am not able to check this before tomorrow morning.

But I am pretty sure, that I already tried this and deleted SHEET = "cur_user"; because it is implied by the data=smf.cur_user statement.

I'll keep you informed.....

hoff_sas
Calcite | Level 5

Hello Anca,

as written yesterday....

Even if I insert the "Sheet = 'cur_user'" statement, the sheet is not updated. And again the log seems fine:

a.) after proc import

NOTE: 314 observations read from file SMF_OLD_USER

...

b.) after merge end deletion of duplicates

NOTE: ....SMF.CUR_USER has 331 observations and 7 variables

...

c.) after proc export

NOTE: ....worksheet "cur_user" successfull created

-> however, the worksheet has 314 ovservations. Not 331!

Any suggestions!?

I assume the problem in incompatibility between the used SAS excel engine and MS Office 2007...

.....I have tried several engines and options.....

If anyone know why this problem occur related to my environment,.....please let me know

kind regards Frank

Tom
Super User Tom
Super User

You are reading from and writing to the same XLSX file?

Do you have more than one sheet in the file?

If so does that ever work?  That is can you really use PROC EXPORT to update a single sheet in an existing file?

If the XLSX file only has one sheet then why not write to a different file name.  Or erase the old version before using EXPORT to create the new one?

hoff_sas
Calcite | Level 5

Hello Tom,

You are reading from and writing to the same XLSX file?

>> yes..I do

Do you have more than one sheet in the file?

>> no it is just one sheet

If so does that ever work?

>> Why it should not? It's a simple proc export. But no. It#s the first time I do so.

That is can you really use PROC EXPORT to update a single sheet in an existing file?

>> beacuse it is just one sheet inside....to update the whole workbook is also a possible solution. - and I do so, wen not specifying "sheet= ".

If the XLSX file only has one sheet then why not write to a different file name. Or erase the old version before using EXPORT to create the new one?

>> I do not think, that this will help. I'd rather suspect a difference in SAS execution process. I'd rather would understand this process instead of building a workaround.

But I will try this and keep u informed.

thanks tom...and greetings

Reeza
Super User

I think is on to something with the sheet specifications.

I've found that I need to drop/delete the old information before I can rewrite over it.

Vince28_Statcan
Quartz | Level 8

I'm not sure this will be of much help but from SAS documentation on the PROC EXPORT procedure

Excel*Excel 97, 2000, 2002, 2003 or 2007
spreadsheet (using the LIBNAME statement)
.xls

.xlsb


.xlsx

* Not available for Microsoft Windows 64-Bit edition.

XLSExcel 97, 2000, 2002, or 2003
spreadsheet (using file formats)
.xls

EXCELCSExcel spreadsheet connecting remotely
through PC FIles Server
.xls

.xlsb

Options for the DBMS=

So anyway, I haven't had any similar scenario to relate to but if you have no constraints for the excel sheets to be saved in excel 2007 format, you could possibly use the DBMS=XLS. I suppose that something similar is used in the back scenes when you use proc export dbms=excel2007 running on W64. There may even be a warning or a note in the log stating that an engine different than EXCEL or EXCEL2007 was used.

You may have to delete old file/create new file if the existing file format is saved as excel2007 instead of 1997-2003 for DBMS=XLS to work.

Good luck

Vincent

Marina
Calcite | Level 5

I think, all you need for you code to work is to use REPLACE option:

dbms=excel replace;

AncaTilea
Pyrite | Level 9

He is using the REPLACE option.

I think one way to work around this issue would be to first delete the current excel file, then re-create it.

Smiley Sad

Reeza
Super User

This worked fine for me, all the data was exported fine to the same excel file in each export. Only 3 not 4 iterations but it works. Try chainging the DBMS to Excel as Marina suggested.

proc export data=sashelp.class outfile="C:\temp\class.xlsx"

dbms=excel replace;

sheet="Sample1";

run;

data class;

    set sashelp.class;

    age=age*2;

run;

proc export data=class outfile="C:\temp\class.xlsx"

dbms=excel replace;

sheet="Sample1";

run;

data class2;

    set sashelp.class class;

run;

proc export data=class2 outfile="C:\temp\class.xlsx"

dbms=excel replace;

sheet="Sample1";

run;

Reeza
Super User

This seems also inefficient, why isn't only the final processed data exported to excel, why all the back and forth?

Vince28_Statcan
Quartz | Level 8

Reeza,

Was your test on a 32-bits OS or 64-bits?

I had no problem doing it either here at work but we're on Win32 and the proc export procedure documentation, as by the few bits that I've pasted above, don't support DBMS=EXCEL (or excel2007 for that matter) on a 64 bit OS.

Vincent

Reeza
Super User

I tested on Windows xp 32bit/SAS 9.2 32 bit/Excel 2010

My home system is Excel 2007- Windows 7 64 bit though, so I'll test it tonight if it isn't resolved before then.

Scott_Mitchell
Quartz | Level 8

Hi Hoff,

Are you limited to only using proc export?  You could use the PC File Server Libname in the following.  Unfortunately this method does not allow you to replace an existing table, so you need to perform a delete before each export.  I am running Win7 64-bit with a 32-bit MS Excel installation and it works perfectly.

LIBNAME XLSFILE PCFILES TYPE=EXCEL PORT=8621 SERVER=LOCALHOST SSPI=NO  TYPE = EXCEL VERSION = 2007 PATH = "E:\CLASS.XLSX" MSENGINE = ACE;

PROC DATASETS LIBRARY = XLSFILE;

  DELETE "CLASS$"N CLASS;

RUN;

DATA XLSFILE.CLASS;

    SET SASHELP.CLASS;

RUN;

PROC DATASETS LIBRARY = XLSFILE;

  DELETE "CLASS$"N CLASS;

RUN;

DATA XLSFILE.CLASS CLASS2;

    SET SASHELP.CLASS;

    AGE=AGE*2;

RUN;

PROC DATASETS LIBRARY = XLSFILE;

  DELETE "CLASS$"N CLASS;

RUN;

DATA XLSFILE.CLASS;

    SET SASHELP.CLASS CLASS2;

RUN;

LIBNAME XLSFILE CLEAR;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2071 views
  • 0 likes
  • 7 in conversation