Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Mainframe SAS - Excel - VB Macro

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1532
Location: Chennai

PostPosted: Tue Sep 18, 2012 1:25 am    Post subject: Mainframe SAS - Excel - VB Macro
Reply with quote

Hi All,

I work on SAS 9.1 and have a task to create a Excel spreadsheet with a pie chart on it.

I am able to create the spreadsheet(numbers and data) using ExcelXP, tagset and proc template, but don't know how to create a Pie Chart from the data.
Googling led to DDE technique and the X command, but I dont know how to use them from mainframe.

Could you please let me know if it is possible to create a XML file with embedded VB macro code, so that the VB macro creates the Pie Chart?

1. ExcelXP tagset creates XML into a output file.(ok here)
2. Edit the output XML file in the next step and insert some statements, so that when XML is opened it executes a VB macro to create a pie chart. (No idea on this part)

Please feel free to correct me or suggest your ideas about how to go about this requirement.

Sample Dataset:

Code:
Gender  Percentage
Male        40
Female      60


Thanks for Reading,
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7314

PostPosted: Tue Sep 18, 2012 3:20 am    Post subject: Reply to: Mainframe SAS - Excel - VB Macro
Reply with quote

Why not just have the macro in the spreadsheet? On "open" of the particular sheet, macro kicks-in and Pie appears. All you do is provide the data.

Process, of course, includes all the good things like checking the header (logical file name, business/data date, run-date and time) and trailer (count of records, hash-totals of value fields, etc).

There's lots of stuff you can do to make it "professional" and easy to use. Use the same rigour on the PC as on the mainframe. You might get "territory conflicts" but if well-designed and specified, there can't be much genuine complaint.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1532
Location: Chennai

PostPosted: Tue Sep 18, 2012 10:17 am    Post subject:
Reply with quote

Hello Bill,
Thanks for your thought on this.

Quote:
Why not just have the macro in the spreadsheet?

The spreadsheet is generated in the form of XML from mainframe, Still have not figured out how to embed macro/VB in XML data file.

Are you suggesting to download the XML from mainframe and then manually run a macro on it? I could do this but it involves some amount of manual intervention and manual work. So looking to automate the whole thing.

Regards,
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1532
Location: Chennai

PostPosted: Thu Oct 04, 2012 12:44 pm    Post subject:
Reply with quote

Hello,
I was able to achieve the requirement "partially" using ODS MSOFFICE2k.

Code:
ods tagsets.msoffice2k_x file="c:\temp\trash\mcro.xls" style=normal
      options( open_macro="'c:\temp\macro.xls'!test" );
  proc print data=sashelp.prdsale(obs=10);
  run;
  ods tagsets.msoffice2k_x close;
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. ISREDIT Macro is not returning back t... nico_neoz CLIST & REXX 21 Sat Nov 18, 2017 7:40 pm
No new posts Mainframe Blocks to Tracks conversion upendrasri JCL & VSAM 1 Sat Nov 18, 2017 10:52 am
No new posts NDM 300 files from mainframe to linux shreya19 All Other Mainframe Topics 2 Thu Oct 26, 2017 11:40 am
No new posts Identifying Interfaces in CICS/mainframe Ashishpanpaliya CICS 5 Fri Oct 13, 2017 3:21 pm
No new posts Mainframe scheduling tool ddhar Compuware & Other Tools 2 Wed Sep 27, 2017 12:48 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us