Help on how to generate output as excel file

Reply
Contributor
Posts: 68

Help on how to generate output as excel file

Hi, I am attaching (inherited, sample) code on generating statistics. I am not familiar with proc printto print = outlist.

Q1- if this is going to outlist...where do I find that in my directory? Can I easily copy/paste to an excel file from that (I can figure this out once I locate it)

Q2-It seems this should be an easy copy/paste to an excel destination file, since that was the method used to build the previous data tables/files...by sheet from an excel source file to an excel destination. Tried, unsuccessful with my present skill set...using copy from html and results, and copy to word then to excel..nada.

Please help/suggest.  Thank you.

Super User
Super User
Posts: 7,401

Re: Help on how to generate output as excel file

Hi,

Looking at the code, it seems to be quite old, and writing text output to a file.  Might I suggest that you take the dataset I1 and then using ods and proc report create an output file that way.  Base SAS: ODS PDF Resources

All the printto is doing is directing some text output to the default SAS output window.

Also, using ods tagsets.excelxp and proc report you can generate an XML file (which can be read by Excel) so gets rid of the need for copying pasting.  In all fairness though, you may be better off just re-writing the code, doesn't seem particularly complicated and could use some coding standards being applied.

Contributor
Posts: 68

Re: Help on how to generate output as excel file

Thanks RW9. I appreciate your forthrightness. There are a total of about 100 tables, and they are use output to a source file with "sheet" built tabs, that are then linked/copied to a destination file. UGH. So on the next go around of these 100 tables for the next year, I plan to use proc report to generate all of them...I understand and have used it and it provides a better formatting mechanism and such. Anyway, we are on deadline, and me generating this code will take more time than I am allowed thus the fidgeting with trying to work with what I have now.

In addition,as might be evident, this code is much longer than what I attached, and though I understand what I am producing, I would not be able to write this code myself and there are 5 of these tables. I will investigate "ods tagsets.excelxp ."

Thanks so much!

Contributor
Posts: 68

Re: Help on how to generate output as excel file

In addition, since I began using this help forum, I have successfully produced what I needed to without conferring with a non-collaborative colleague who wrote these programs, If I can get this last bit done, I would be thrilled. Full disclosure.

There is a way these 5 tables made it into the same destination file, from what was produced using the code, I am almost certain, since they are all there with the other 100 tables, which are then delivered to our 508 professionals for final publication in pdf.

Super User
Super User
Posts: 7,401

Re: Help on how to generate output as excel file

Well, in a basic SAS setup I would assume (unless it gets explicitly changed in the code) your temporary area, could be in \Documents and Settings\user-name.

Contributor
Posts: 68

Re: Help on how to generate output as excel file

I'm there...looking. Thanks!

Contributor
Posts: 68

Re: Help on how to generate output as excel file

Yes sir, it was there, but the format to copy and paste not different from the sources I ably captured on my maching (SAS). Thank you!!!

Super User
Posts: 17,815

Re: Help on how to generate output as excel file

You say you're copying from HTML, do you have the ODS Listing destination enabled? If not try that.

Tools>Options>Results ( I think)

In previous versions of SAS it was the default output and probably what the code is designed for.

Copying and pasting from listing is easier.

Contributor
Posts: 68

Re: Help on how to generate output as excel file

Agree and yes, I did. the copy and paste stinks though. The formatting capability skews terribly. Trying different pasting special options were unsuccessful.

Thank you, Reeza!

Contributor
Posts: 36

Re: Help on how to generate output as excel file

If the output is "linked/copied to a destination file" then why is the format important to the source file?

Perhaps you can use your non-formatted output and link it to your formatted excel sheet.


Bruce



Contributor
Posts: 68

Re: Help on how to generate output as excel file

Sorry, I should be more clear. On the first 100 tables the output was to a source file, then link/copy to a destination file, both excel. The same destination file holds tabs for these last 5 tables, AND skip over to the code. The code for these (5) tables is not written for "sheets" like the first 100 tables. But they all end up in the same "destination" file, which leads me to connect the dots, that there must be a copy/paste method to accommodate from the outlist output for the last 5 tables....to the same destination file. Hope this helps.

Thanks for asking Bruce.

Contributor
Posts: 68

Re: Help on how to generate output as excel file

For closure, I was not able to find an expeditious manner by which to convert html to excel, nor taking the code I posted and exporting to excel...what I did was create a new dataset keeping the vars I needed and then doing the old copy past from excel source doc to excel destination...UGH. Thank you everyone fore responding. Converting the 100+ programs output to proc report will follow, for next years data.

Contributor
Posts: 68

Re: Help on how to generate output as excel file

Thank you for contributing here and I hope I properly "liked" every response!

Issue: "Edit links" usage on an excel file from SAS to excel.

1. My sas output generates a "tables" excel file where tabs are created for each table. This is what I call my "source" file.

2. I copy/paste/link each table to a "destination" file that is formatted for adobe and 508 and that jazz...(yes thinking proc report already!).

3. I manually link/paste each table to the destination file. (wow 75 tables).

4. Each time I regenerate a table then when I open the destination file (after opening the source file) the destination/tables should update (not sure its happening properly).

Issue: this is not working properly!

Why (I think): the destination file was inherited and I do not think I properly updated the links, then I broke a link and now cannot relink. thinking linking tutorial likely...

Regardless: when I open edit links, there are two files there from the previous user. One is the "tables" file location--get that. Two is another file, that has a name that does not sound familiar.

Q: Should there be two files in edit links to enable this process properly, and if so, what should the other file be?

Super User
Posts: 17,815

Re: Help on how to generate output as excel file

Don't link to external files.

Create your template (destination) file and then copy it for that particular report.

Export directly to that Excel file and have internal links ie

Sheets in workbook

Page 1 - Report   (data in this sheet is linked from Page 1 Data)

Page 1 - Data

You can export directly to an Excel file using the libname method and/or DDE and/or the following VB Script:http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Ideally, you would use SAS to generate the full report to Excel using the new ODS Excel option in SAS 9.4 - latest release I believe.

Contributor
Posts: 68

Re: Help on how to generate output as excel file

Understand the not linking to the external file, as meaning, link only to the source "tables" file. However, the destination file is formatted for presentation purposes, while the tables source file is rudimentary (working draft if you will).

I do have my own destination folder, which is formatted, but not sure what you mean by "copy" it for that particular report.

[ these series of tables makeup up one report, that is published as two files--

adobe and excel]

The source file prints fine now, with the sheets each generating properly as tables, no problem except method generates generic text/font/... thus the "destination" formatted file.

I do believe I need to use the new ODS excel option you mention from 9.4.

Thanks so much...awesome information!

Ask a Question
Discussion stats
  • 20 replies
  • 721 views
  • 4 likes
  • 6 in conversation