Proc Export Replacing Excel Sheet ??

[KevinC_]

Hello Everyone,

I am trying to export data from SAS to an existing sheet in an Excel doc called Cust_Report.  The sheet name is "MTD".  I would like to overwrite the existing sheet but it creates a new sheet called "MTD1" instead.  Does anyone have any suggestions on how to replace an existing sheet in Excel?  I use SAS 9.3. 

Thank you so much in advance for any input!  Here is my code:

PROC EXPORT DATA = Total

OUTFILE = "C:\My_docs\Cust_Report.xlsx"

DBMS=EXCEL2000 REPLACE;

Sheet = "MTD";

QUIT;

Thank you!

[Cynthia_sas]

Hi: I wonder whether your mismatch between DBMS= and the file extension of XLSX is making things difficult? I thought that XLSX files were part of Office 2007, NOT Office 2000. So I thought you needed DBMS=Office2007 for .XLSX. See Example 4 in this Tech Support note: 42981 - Microsoft Excel files that have the .xlsx extension cannot be exported.

   I no longer have SAS 9.3 to test with and I know that there were some changes to the EXPORT procedure syntax that were needed because of "bitness" issues (32 bit Office, 64 bit SAS for example) that might also make Export not work correctly.

  You might want to work with Tech Support so they can replicate your SAS, your Office and your bitness to figure out what is happening.

cynthia

[cwcaulkins]

"bitness"? Is that a new "SAS-ism"?

[pawan]

Hi Kevin,

I face this issue every often and I dont think it is any "bit" issue. But I did overcome this by a very awkward way. Suppose If want to load data into an Excel having a sheet named as "MPT" then I wouldnt create the template first and load the data rather I would generate the Excel first and then use the generated Excel as a template(this will have the sheet created with name as "MPT" and make necessary formats to the template) and keep loading the data from next instance.

Give a try, nothing wrong.. it's a magic

All the Best!!

[KevinC_ · Accepted solution]

Thank you guys so much for your suggestions !

Pawan, I will try your 'template' method.  This is what I came up with in the meantime:

LIBNAME xls EXCEL "C:My_docs\Cust_Report.xls"

PROC Datasets lib = xls nolist;

     delete MTD;

Quit;

Data xls.MTD;

set cust_data;

run;

LIBNAME xls clear;

Proc datasets deletes the existing 'range' and the subsequent data steps creates/overwrites the Sheet MTD.

Cheers

[Gregor]

Unfortunately, this does not work in my case. I use a similar code but within a loop, where I create an excel file in every step:

%let copycmd1 = COPY "&manpath.\Stoch_Vorlage.xls"

"&manpath.\Stoch_&klasse..xls" ;

　

data _null_;

option noxwait;

X &copycmd1;

run;

libname excel1 excel "&manpath.\Stoch_&klasse..xls";

PROC Datasets lib = excel1 nolist;

delete KLASSE;

Quit;

　

Data excel1.KLASSE;

set ref_&jj;

run;

　

　

　

libname excel1 clear;

　

PROC Delete data = work.ref_&jj;

%end;

Log tells me my prepared data sheet 'KLASSE' would not exsist and creates a new one 'KLASSE1'

NOTE: Libref EXCEL1 was successfully assigned as follows:

Engine: EXCEL

Physical Name: Q:\[correct directory]\Stoch_12_42 .xls

MPRINT(ALLKLASSE): PROC Datasets lib = excel1 nolist;

MPRINT(ALLKLASSE): delete KLASSE;

MPRINT(ALLKLASSE): Quit;

NOTE: The file EXCEL1.KLASSE (memtype=DATA) was not found, but appears on a DELETE statement.

NOTE: PROCEDURE DATASETS used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

Result: in Stoch_12_42 .xls, KLASSE is not replaced, but the data has been added in a new sheet KLASSE1.

There are references from graphics to KLASSE which I do not want to calculate individually but to be created in each loop step respectively.

Thanks

Gregor