BookmarkSubscribeRSS Feed
JasonNC
Quartz | Level 8

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

4 REPLIES 4
art297
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Super User

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!

 

 

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 450 views
  • 0 likes
  • 3 in conversation