Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1475
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: 7306

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: 1475
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: 1475
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
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts FTP from Mainframe to Unix - with CRLF vasanthz All Other Mainframe Topics 5 Thu Mar 23, 2017 5:12 am
No new posts Want to pull the job log into a Mainf... Jishu Mukherjee Compuware & Other Tools 9 Tue Mar 21, 2017 4:48 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Innovations in Your Mainframe Sort Arun Raj PC Guides & IT News 4 Wed Mar 15, 2017 10:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us