Can we export a SAS output file (not a dataset) to a specific worksheet (“tab”) in a Excel workbook?

[Mirisage]

Hi Community,

Below is a part of a long code. This generates a SAS  output file (as opposed to SAS data set) named  Table_I_want_finally (Astounding, mkeintz and Linlin are acknowledged).

proc summary data=vtemp   nway missing;

format date date9.;

class date;

var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA write_off;

output out=Table_I_want_finally(drop=_:)

    sum=;

run;

This is the SAS output file generated by above code which shows dollar sums in each delinquency bucket by month.

Current_date	Current	One_to_30	Thirty_to_60	Sixty_to_90	Ninety_plus	NPNA	write_off
28FEB2010	15	7	100	50	33	44	10
31MAR2010	2	3	6	10	27	6	15
30APR2010	33	44	55	88	100	200	10
31MAY2010	100	200	250	125	55	50	50
30JUN2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31JUL2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31AUG2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
30SEP2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31OCT2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
30NOV2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31DEC2010	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31JAN2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
28FEB2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31MAR2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
30APR2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31MAY2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
30JUN2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31JUL2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31AUG2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
30SEP2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31OCT2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
30NOV2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31DEC2011	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this
31JAN2012	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this	I didn’t fill this

Question:

I need to send this SAS output to a MS Excel “tab” named “Source_Data_FCIB_but_noBank60”. In Excel tab, Cell A5 should be the starting location that this output dataset should be pasted.

My effort:

I created this code.

ods html file="\\abcde-a8-sas01\xxx\Table_I_want_finally.xls";

proc summary data=vtemp   nway missing;

format date date9.;

class date;

var Current One_to_30 Thirty_to_60 Sixty_to_90 Ninety_plus NPNA write_off;

output out=a.Table_I_want_finally(drop=_:)

    sum=;

run;

ods html close;

Problem:

1). I can see a physical Excel file named Table_I_want_finally.xls was created in

\\abcde-a8-sas01\xxx\

But the file is empty.

2). Is there any method to export the SAS output data to a specific tab in MS Excel. And can we specify a specific Cell in Excel tab that the output file to be started its pasting.

Your help is greatly appreciated.

Thanks

Mirisge

[art297]

Mirisage, The output from proc summary IS a SAS dataset.  Why not just use proc export?

[Cynthia_sas · Accepted solution]

Hi,

  When you use ODS, you are not creating a true, binary Excel file. You are merely creating an ASCII text file that Excel knows how to open and render. With ODS your choices are:

1) ODS CSV

2) ODS HTML or other HTML-based destinations

3) ODS TAGSETS.EXCELXP

  The default with PROC SUMMARY is to NOT create a printed report or printed output. So you would have had to follow the PROC SUMMARY step with a PROC PRINT step in order to get output in your HTML file so the HTML file could be opened with Excel.

  On the other hand, Art had a very good suggestion. If you want to add your output table into an Excel worksheet, in a new or existing workbook, then either PROC EXPORT or the LIBNAME engine would be the method to choose. With ODS methods, every time you invoke ODS, the output file gets created over again. If the FILE= output file already exists, it is overwritten. And with ODS methods, you have no control over the cell into which the output gets written.

  Even with PROC EXPORT or the LIBNAME engine, there is no way to write directly to a specific cell unless you use named ranges in the Excel file.  With DDE methods, you can write to a specific cell in an existing Excel worksheet.

  Now that you have your output table, you will have to figure out whether export methods (PROC EXPORT or the LIBNAME engine), ODS methods or DDE methods are the best for you. I know that each of these methods has been written about on the forum, so you should find many resources.

cynthia

[Mirisage]

Hi Cynthia and Art,

Thanks very much to both of you.

Hi Cynthia,

I really appreciate your detailed description.

Regards

Mirisage