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
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.
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.....
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
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?
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
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
I think, all you need for you code to work is to use REPLACE option:
dbms=excel replace;
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.
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;
This seems also inefficient, why isn't only the final processed data exported to excel, why all the back and forth?
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
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.
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;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: