Issue: Need specifically formatted state and federal forms - have not able get the right format with PROC REPORT. Can I pull the necessary data into EG, send it out to a prebuilt MSWord merge document and bring the filled in forms back into EG to send out as PDFs? I like the idea of keeping the entire process in EG - I can build the forms easily in Word and then merge the data into them. Do you know if this can be done and if there are papers or some guidance out there somewhere? Thank you!
I know that IF you build the Word merge document with the fields defined, you can use a CSV file as the data source for the Word MERGE document. ODS CSV can build you a CSV file with the data values to use for the Word MERGE.
I have done this part of what you ask, so I know that it is possible. I started the merge manually in Word, but I understand you could have a VB script provide the CSV file name to the merge in an automated fashion.
You say you want to bring the "filled in forms" back into EG to send out as PDFs??? I find this confusing...Word is going to build Word documents as a result of using the CSV file with the Word merge document. Where are you going to get PDFs??? I thought you needed to have some kind of plug-in for Word in order to build PDF files from Word documents.
Although you can use ODS PDF to build PDF files -- if I understand your process, there's a step missing between performing the merge and doing the emails. I think the missing step is that somehow, your Word documents that are the result of the merge will need to be converted to PDF files -- and I believe you must use a Word/Office method to accomplish this.
SAS Enterprise Guide would NOT be able to -read- the PDFs, but you could write a program to do the emailing of the PDFs, assuming you knew the PDF file name(s) -- because when you use the EMAIL engine, you can name any file to be an attachment when you send the email. Most of the time the file that you attach is created with ODS, but you could use any file name for the attachment. So, assuming you figured out how to create PDF files after the merge, then you could attach these PDF files to an email program.
Thank you for your response Cynthia. I, too, have done MSWord merges using various types of input (excel file, etc.). I'm not sure I totally understand everything you said, but I do know that what my hope is, is to keep this as a project within Enterprise Guide, from start to finish. I'm wondering if the merged documents can be 'sucked' into EG somehow (for lack of a better word) so that EG can make the PDFs, which it is good at. My wish list would be this:
1. Pull the data into EG using an ODBC Teradata connection.
2. Write SAS code to build the datasets to feed the forms.
3. The form templates would be prebuilt and available in MSWord. (Below is the part I don't know.)
a) Push the data out to MSWord and build the documents using MERGE and then pull the completed forms back into EG? Or;
b) Pull the template from MSWord into EG and fill it in within EG?
4 Whatever the outcome of #3; export to .PDF files.
What a great concept! PROC REPORT is good, but doesn't have the formatting capability to get to some of the necessary formatting required my many users. Combing the power of EG with the flexibility of formatting in Word would be AWESOME. Has anyone tackled that - or would like to?
Here's the issue/problem that I see with what you want to do...specifically 3a and 3b and 4.
You say that the form templates would be "prebuilt and available" in MS Word -- therefore, these are WORD templates or .DOT files. This is a proprietary Microsoft Format. SAS knows how to read proprietary Excel files and proprietary Microsoft Access (.mdb) files -- but Word is a document -- not a representation of a data source.
So, for this reason while you CAN "push" the data out of EG (by using ODS CSV) and you can build the documents using Word and the MERGE capability of Microsoft Office. You cannot "pull the completed forms back into EG." The completed forms will be Word documents....in Microsoft Word proprietary format.
To see what Microsoft Word documents look like, perform the following experiment. Go to MS Word. Type a paragraph of text into the document. Save the file as a .DOC file; then close Word. Next, open Notepad and navigate to the location where you saved the Word Doc. Open the .DOC file with Notepad. Much of the file will be unreadable because it is stored in proprietary format. SAS cannot read MS Word .DOC files. So, this fact makes your 3a not really possible. After your MERGE documents have been created in Word, you will not be able to "pull the completed forms back into EG".
OK...now let's look at possibility 3b: Pull the template from MSWord into EG and fill it in in EG. Again. the mail-merge template document is a proprietary Word document. So for the same reasons that you could not pull the merged forms into EG, you can't pull the mail-merge template into EG and just "fill it in".
Now, let's look at #4. I agree with what you said that EG is very good at making PDFs -- but WHAT is EG making PDFs from??? EG is making PDFs from the results of the EG tasks or SAS code in your project. EG isn't just acting as a front end to Adobe Acrobat or Acrobat Distiller for all purpose PDF creation.
EG (and ODS) need for SAS to create output that can be routed to the ODS PDF destination. EG can't act on files that just live on your system (like .DOC files). If you have the full Adobe Acrobat or Acrobat Distiller, then you may have a plug-in -- but a Word plug-in -- that would allow you to create PDF files from your Word docs. But that capability is a Word plug-in capability...because you want to turn Word documents into PDF documents. That's between Word and Adobe -- EG and ODS do not enter into the picture here....in fact...they CAN'T enter into the picture here.
So, since #3a and #3b can't happen in the EG world, #4 will not work either. EG is not technically "exporting" to PDF. Exporting, in the SAS world, implies exporting data to another format, like exporting your SAS table to Excel or Microsoft Access or Oracle format. When ODS creates a PDF file -- it is not EXPORTING output to PDF -- It is taking SAS results and producing a PDF file that contains the results of the SAS process. But the PDF can ONLY contain the results from a SAS process.
You might be able to write a VB script to start up the merge automatically and then, if you have the Adobe Acrobat plug-in for Word, you could create PDFs from the merged documents under control of the script.
But remember that even though EG is running on your local machine and Word is on your local machine -- where is SAS??? SAS is probably on a server that EG knows how to talk to. Your SAS code doesn't usually run locally -- your SAS code usually runs on the server machine. Word may or may not be on THAT machine.
Some kind of scenario like this might be possible: You create a custom task for EG -- within that custom task, you create a CSV file and return the CSV file to your local machine. Then the custom task might invoke the VB script to start up the Word mail-merge using the CSV file. And the custom task -might- be able to issue the appropriate batch commands to convert the Word document to PDF form -- BUT, the custom task would not technically be "sucking" the Word final merged documents "into EG" -- the custom task would be interacting with the files on your local machine both the EG created results and the non-EG files.
In addition to building forms in Word, I believe that Adobe Acrobat also has a way to design a PDF document that contains "fillable" areas and the data that fills in those areas is defined by XML tags that Adobe products know how to render into the form. However the Adobe PDF form route or the Word form route or the custom task route all mean that some part of your process is going to happen outside of EG and that you will not be able to keep this as a project within EG from start to finish.
And, yes, people have come up with a way to combine the power of EG with the flexibility of formatting in Word. It's called the SAS Add-in for Microsoft Office. Using the SAS add-in, you can open Word and from within Word, run a task or Wizard, just like the tasks or Wizards in EG in order to perform analysis and get results right in Word or Excel or PowerPoint (and soon, in Microsoft Outlook).
The SAS Add-in for Microsoft Office is one of the components that are available when you are using the SAS Platform for Business Analytics (also known as the BI Platform).
Cynthia . . . thank you for your great explanation! I've been busy putting out fires and am just able to get back to this. It appears that my vision needs to be adjusted, but you give some good possibilities. Even with having to go outside of EG - our process will be greatly improved, but I needed to explore that possibility. I can't say thank you enough for taking the time to write this all out and help me to understand the 'behind the scenes' activity that supports your explanation. Many thanks, Karen (PS. I'll most likely be back with more questions as I continue through this. )