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: 1562
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: 1562
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: 1562
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 Transfer of mainframe file to PC in Z... maxsubrat JCL & VSAM 4 Thu May 17, 2018 11:35 am
No new posts IBM Pcomm macro startmacro command do... JGregoire IBM Tools 0 Wed May 09, 2018 12:16 pm
No new posts converting a mainframe file with ASA ... Lynne Schuler All Other Mainframe Topics 5 Wed Apr 04, 2018 9:44 am
No new posts Print call stack in Mainframe C DIPANKAR SAHA ABENDS & Debugging 2 Thu Mar 15, 2018 2:58 pm
No new posts Mainframe developer / Testers in Pune girish_kanswa Mainframe Jobs 0 Wed Feb 14, 2018 1:35 pm

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