SAS 9 export into Excel 2003

[AlboMrsP]

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]

Why not use PROC EXPORT?

 

[AlboMrsP]

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]

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

 

[LarrySteele]

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]

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]

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