topic Re: Help on how to generate output as excel file in SAS Programming

Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 14:23:58 GMT

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.

Re: Help on how to generate output as excel file

RW9 — Thu, 05 Feb 2015 14:36:21 GMT

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.

Re: Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 14:54:20 GMT

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!

Re: Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 15:02:11 GMT

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.

Re: Help on how to generate output as excel file

RW9 — Thu, 05 Feb 2015 15:12:50 GMT

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.

Re: Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 15:15:23 GMT

I'm there...looking. Thanks!

Re: Help on how to generate output as excel file

Reeza — Thu, 05 Feb 2015 15:17:43 GMT

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.

Re: Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 15:28:36 GMT

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!!!

Re: Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 15:31:13 GMT

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!

Re: Help on how to generate output as excel file

BigD — Thu, 05 Feb 2015 15:49:50 GMT

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

Re: Help on how to generate output as excel file

lola — Thu, 05 Feb 2015 16:09:03 GMT

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.

Re: Help on how to generate output as excel file

lola — Thu, 12 Feb 2015 16:38:14 GMT

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.

Re: Help on how to generate output as excel file

lola — Wed, 27 May 2015 17:53:27 GMT

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?

Re: Help on how to generate output as excel file

Reeza — Wed, 27 May 2015 18:24:24 GMT

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.

Re: Help on how to generate output as excel file

ballardw — Wed, 27 May 2015 18:26:18 GMT

If you don't have an example of the second file then you may spend a lot of effort matching things up unless there is an actual Excel function that will report on which cells have the links. (Not available the last time I was working on such a thing).

It may be worth going to the users and asking what additions or changes they might like to this report result and use that as an opportunity to update the whole process. The whole links between documents that may be renamed, deleted, not copied with a project makes this approach difficult to maintain, as you have discovered. Since you deliver a PDF to the end users you might be able to create that directly using ODS PDF.

Re: Help on how to generate output as excel file

lola — Wed, 27 May 2015 21:20:10 GMT

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!

Re: Help on how to generate output as excel file

lola — Wed, 27 May 2015 21:28:46 GMT

Correct, I don't have that second file and not sure what it should be/look like...got it.

Agree with going to users, have made numerous changes already, and since I have to redo 100 tables, thought I would stop and take the time to more efficiently do this part, while I wait for the data to run yet again (g!). You're right also about this whole archaic linking stuff. But pdf is only half of the solution, need to publish an excel file too.

Thanks so much for this informed reply!

Re: Help on how to generate output as excel file

RW9 — Thu, 28 May 2015 07:54:57 GMT

Blimey this post was a way back ;O)

What it sounds like your problem is, and its something facing everyone, is the use of Excel for purposes it was never really intended. I.e. it is not a data transfer format, nor a database, nor a reporting utility, or a data entry utility, or a programming environment - all of which I have seen in the past. Specific software is designed to do specific tasks. So and Oracle database is designed to do databasing, JReview, SAS Web Report Studio are designed to provide review outputs.

Now you have inherited an Excel file, but are missing parts. Off the bat you are heading into problems. Me, I would go back and ask the customer what they want, write down a functional design specification and follow Software Development Lifecycle principals. If they have to have Excel, and most higher levels need many Excel files to make their existence feel worthwhile, then decide on what technology you will use. ODS tagsets.excelxp is pretty easy to use, and can produce nice formatted reports, but lacks a lot of the functionality e.g. linking. Libname to Excel I personally haven't used much. DDE whilst still works, is old and may not be supported. But you are not limited there, you could write VBA macros witihin your Excel template file, or in a separate one, and write the code in there to pull the data from saved CSV files into your Excel file. So rather than pushing from SAS, pull from Excel. In SAS you would only need to export the data to CSV, all other processing is done in VBA. Or maybe you can convince the customer that having some sort of web report setup is a good idea, they wouldn't be limited to what is designed then, but then there is a cost and resource. Etc. many options, but you really have to start with "what is it you, as a customer, want" question and build specs out from there.

Re: Help on how to generate output as excel file

Kurt_Bremser — Thu, 28 May 2015 08:40:18 GMT

I strongly second 's opinion. When confronted with a mess like this, go back to square one and define the requirements (or have them defined). From that, select the right toolset to solve the issue.

Questions for the customer:

- what insight do you want to gain?

- who will need to have access to the report/data/analysis?

- what tools are available to those persons?

Very often I have solved the "I want an Excel like that" request by supplying a simple HTML page that's updated daily/weekly/monthly and posted on the data warehouse's internal web server. Because they wouldn't do anything but attach the .xls to a mail and send it to several people.

Re: Help on how to generate output as excel file

lola — Thu, 28 May 2015 13:37:29 GMT

RW9, agree. I understand your analysis and support your conclusion. I continue to find myself in a reactive mode to what is here and now, with no time for proactive data management. Its seems to fall to too few resources continually. I readily admit I will have to finish this annual project with this tool, but in the same breadth must find a long-term solution for next years data which will begin immediately following this deliverable. Thank you for your insight!