SAS 9 export into Excel 2003

AlboMrsP · Feb 10, 2011

Hi,
I'm currently trying to get SAS 9 to export to Excel 2003 on a specific tab without using DDE.

I've scoured the net and got as far as the below:

libname myxls "c:\update.xls";
data myxls.data;
set work.update2;
run;

libname myxls clear;

This will add all the headers in the update2 dataset to the correct Excel file and tab in the workbook, but, no data :-(

Does anyone know how to do this? Any help you can provide would be greatly appreciated and will more than likely stop me hitting my head on a wall!! ;-)

klaz2002 · Feb 10, 2011

Why not use PROC EXPORT?

AlboMrsP · Feb 11, 2011

Hi Klaz2002

I need to output 2 different datasets to the same excel sheet on different tabs.

Is this possible in a proc export?

jj72uk · Feb 11, 2011

You could look into doing a proc append into a XL sheet..

LarrySteele · Feb 18, 2011

May I suggest: thread376-1335588: export to multiple excel tabs.

It's a bit long and the solution worked for SAS 8 on zOS. Unfortunately, I haven't work with SAS or the mainframe for quite some time and wouldn't be able to assist. Hopefully you'll be able to find enough information to help you accomplish your goal.

AlboMrsP · Mar 1, 2011

Thanks all for your help. I managed to get it to work with the following code:

LIBNAME wrkbk EXCEL 'c:\update.xls';

/*deletes existing named range in Excel to allow overwriting in the next step*/

proc datasets lib = wrkbk;
delete data;
quit;

/*Exports data to excel and creates data named range (and tab if it doesn't exist already)*/

data wrkbk.data;
set update2;
run;

/*Clears libname to allow the Excel file to open, without this the file would be locked*/

libname wrkbk clear;

LarrySteele · Mar 1, 2011

Glad you got it sorted out. Thanks for posting the solution for others.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SAS 9 export into Excel 2003

AlboMrsP

MIS

klaz2002

Programmer

AlboMrsP

MIS

jj72uk

Programmer

LarrySteele

Programmer

AlboMrsP

MIS

LarrySteele

Programmer

Similar threads

Part and Inventory Search

Sponsor