topic Re: more than one proc export statements in one program in SAS Procedures

more than one proc export statements in one program

hoff_sas — Thu, 25 Jul 2013 09:18:46 GMT

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

Re: more than one proc export statements in one program

AncaTilea — Thu, 25 Jul 2013 11:33:17 GMT

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.

Re: more than one proc export statements in one program

hoff_sas — Thu, 25 Jul 2013 13:17:47 GMT

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.....

Re: more than one proc export statements in one program

hoff_sas — Fri, 26 Jul 2013 10:45:02 GMT

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

Re: more than one proc export statements in one program

Tom — Fri, 26 Jul 2013 13:05:39 GMT

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?

Re: more than one proc export statements in one program

hoff_sas — Fri, 26 Jul 2013 13:30:13 GMT

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

Re: more than one proc export statements in one program

Vince28_Statcan — Fri, 26 Jul 2013 14:09:50 GMT

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.

XLS

Excel 97, 2000, 2002, or 2003
spreadsheet (using file formats)

.xls

EXCELCS

Excel 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

Re: more than one proc export statements in one program

Reeza — Fri, 26 Jul 2013 14:54:42 GMT

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.

Re: more than one proc export statements in one program

Marina — Fri, 26 Jul 2013 16:37:37 GMT

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

dbms=excel replace;

Re: more than one proc export statements in one program

AncaTilea — Fri, 26 Jul 2013 17:03:02 GMT

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.

Re: more than one proc export statements in one program

Reeza — Fri, 26 Jul 2013 17:28:35 GMT

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;

Re: more than one proc export statements in one program

Reeza — Fri, 26 Jul 2013 17:29:38 GMT

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

Re: more than one proc export statements in one program

Vince28_Statcan — Fri, 26 Jul 2013 17:59:40 GMT

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

Re: more than one proc export statements in one program

Reeza — Fri, 26 Jul 2013 19:04:32 GMT

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.

Re: more than one proc export statements in one program

Scott_Mitchell — Fri, 26 Jul 2013 23:22:26 GMT

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;