topic Re: Problem of this export in SAS Procedures

Problem of this export

Mike_Davis — Tue, 11 Sep 2012 13:17:07 GMT

Hello everyone,

I have a problem to update an exist excel file,I attached an excel file and use the SAS code below to update this file.

I have get a result not as my expected,because the old sheet a didn't replace by sashelp.class but create a new sheet named a1.

Please help

Thanks!

Mike

PROC EXPORT DATA=sashelp.class

OUTFILE = "C:\Temp\test8.xls"

DBMS = /*EXCEL2002*/excel REPLACE;

SHEET = "a";

RUN;

Re: Problem of this export

Linlin — Tue, 11 Sep 2012 14:23:40 GMT

Mike,

I don't have any problem to run your code and replace the old same named file.

you can try the modified code:

libname myfile "C:\Temp\test8.xls";

proc sql;

drop table myfile."a$"n;

quit;

libname myfile clear;

PROC EXPORT DATA=sashelp.class

OUTFILE = "C:\Temp\test8.xls"

DBMS = excel REPLACE;

SHEET = "a";

RUN;

Re: Problem of this export

Mike_Davis — Tue, 11 Sep 2012 15:53:51 GMT

Hi linlin,

The problem still happen,

when I followed your method, firstly the drop table statement ONLY remove the content of sheet a to an empty sheet(didn't delete the sheet but just replace it with an empty sheet)
then still create a new sheet named a1 using sashelp.clas.

Thank you

Mike

Re: Problem of this export

Reeza — Tue, 11 Sep 2012 16:00:27 GMT

http://support.sas.com/kb/20/923.html

If you scroll to the bottom of the the communities, in very small fine print you'll see how to open a track with tech support.

Re: Problem of this export

Mike_Davis — Wed, 12 Sep 2012 17:08:42 GMT

FILENAME MyFile "C:\temp\aa\cc\test8.xls" ;
DATA _NULL_ ;
rc = FDELETE('MyFile') ;
RUN ;

FILENAME MyFile CLEAR ;

PROC EXPORT DATA=sashelp.class

OUTFILE = "C:\Temp\aa\cc\test8.xls"

DBMS = excel2002 REPLACE;

SHEET = "a";

RUN;

this will delete all sheet including b,c,but work for a