Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

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=_Smiley Happy

    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=_Smiley Happy

    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


Accepted Solutions
Solution
‎08-28-2012 12:22 AM
SAS Super FREQ
Posts: 8,864

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

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

View solution in original post


All Replies
PROC Star
Posts: 7,471

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

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

Solution
‎08-28-2012 12:22 AM
SAS Super FREQ
Posts: 8,864

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

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

Super Contributor
Posts: 338

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

Posted in reply to Cynthia_sas

Hi Cynthia and Art,

Thanks very much to both of you.

Hi Cynthia,

I really appreciate your detailed description.

Regards

Mirisage

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 368 views
  • 0 likes
  • 3 in conversation