BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7
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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

3 REPLIES 3
art297
Opal | Level 21

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

Cynthia_sas
SAS Super FREQ

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
Obsidian | Level 7

Hi Cynthia and Art,

Thanks very much to both of you.

Hi Cynthia,

I really appreciate your detailed description.

Regards

Mirisage

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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