Using SAS DDE and VB code

[JasonNC]

Hi,

I want to know some good resources for achieving a output where i need to create a output using sas DDE and VB code.I want to create a template in excel and when sas outputs the data the template can be used to create the output.

Any help would be appreciated

[art297]

You don't need DDE for that and, in fact, don't even have to write the VB code. A couple of us already did that for you.

Take a look at the freely available macro at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

HTH,

Art, CEO, AnalystFinder.com

[JasonNC]

Wow that's awesome.I am including this macro in the job through %include.Usually i do remote submit but this macro didn't work while i did remote submit.I just submited the job then it ran.I am using macro variables in the folder destination for creating the output

I want to create the file on shared drive not on my local drive

%INCLUDE "\\corp\....\exportxl.sas";

%exportxl( data=&t1._Data
template=\\corp\.....\Template.xltx,
templatesheet=sheet1,
outfile=\\corp\....\&Yr\&Mthyy\Research_&Mthyy..xls,
type=N,
usenames=N,
range=A2,
sheet=Rsch);

1) when i did remote submit the job didn't work.Why/

2) I submitted the job but got the following error

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

What mistake I am doing?

[art297]

I've never used rsubmit to run the macro, thus can't be sure of the reason. One possibility, though, is that you'll need to include the server_path parameter. It's described in the paper that's on the same page as the macro.

Art, CEO, AnalystFinder.com

[Reeza]

I did a presentation on this a few years ago, Coordinating Complex Reports Using SAS. 

https://statgeeks.wordpress.com/2015/04/16/sample-work/

We've switched to using @art297's macro instead of DDE for exporting in most spots these days. 

We still use DDE to automatically convert the excel spreadsheet into PDFs and creating graphs in Excel. Sometimes the number of graphs can vary, not accounted for in this presentation or the link to the reports below. The VB code is embedded into the Excel workbook. 

The steps in my process were:

1. Copy the template to a new file with new name for the report. This workbook maintains the embedded VB code. 

2. Export the data from SAS to Excel, as required. Multiple steps may be required here depending on the number of tables needed.

3. Use DDE to open and run the VB code for graphs

4. Convert Excel file to PDF sections, using DDE + VB

5. Go to Step 1 & 100+ for custom reports with formatting and customized texts. 

Reports are here if you're curious 🙂

http://www.health.alberta.ca/services/PHC-community-profiles.html

Good Luck!