Converting to SAS ODS Using Q&R Saved Macros

The discussion and steps described in section 2.8.6 assume that all the query steps from Q&R were replicated in a structured MICF inquiry. For example, the File Selection, Sorting, Summarizing, and any other data manipulation the inquiry needs.
This section will describe a method to use the Q&R macro SAS code so you don't have to replicate each step in MICF which will save conversion time. This involves saving the Q&R SAS macro code, uploading to the mainframe, and then a MICF step to point to that code. The steps to produce the ODS code to create the chart graphics are the same as in the prior section, but will be replicated here for clarity and flow.
  1. Open the Q&R query to convert in the Q&R workstation client. For example purposes, we will call this sample query, 'ABCWRT'.
  2. Select File, Save Macros. A 'Save Macros As' screen will pop up with the name of the Q&R query with a QRM file extension (ABCWRT.QRM). Verify the target directory. By default, this points to your 'My MICS Queries' directory unless the default was changed at some point.
  3. Upload the file, ABCWRT.QRM, to the mainframe and store it in the sharedprefix.MICS.SOURCE library as member 'ABCWRTS'. The 'S' suffix at the end of the member names stands for Query Source. The uploaded member will look something like the following:
    /* !!! MQR START !!! Version: 12.9.14161.1546 */ /* Query Name: ABCWRT, Query Title: Hourly ABC */ /* Response Time */ /* Query Generated on: 03/28/18 01:51:53 PM */ /* Query Generated by: userid */ /* */ -> LIBNAME MCOLIB 'your.sharedprefix.MCOLIB' DISP=SHR; -> LIBNAME PMUOLIB 'your.unit.prefix.MUOLIB' DISP=SHR; -> OPTIONS FMTSEARCH=(WORK.FORMATS MCOLIB.USERFMT1 MCOLIB -> MCOLIB.USERFMT2 -> PMUOLIB.USERFMT1 PMUOLIB.MICSFMTS PMUOLIB.USERFMT2 -> LIBRARY.FORMATS); %LET WSVer = 12.9.14161.1546; %LET MQRQNAME = ABCWRT; %LET MQRQTTL = %NRBQUOTE(Hourly ABC Response Time); %LET MQRQUSER = userid ; %MQRQINI(DEBUG=NO); /*!!!MQR!!!S<START STEP>D<File Selection>*/ %LET FILE1=MQR01354.WLMSEC01-12; . . . (many SAS steps to read, sort/summarize, . . . and manipulate data) . . . (in this example, the last temporary work . . . file created and input to the Data Extract . . . for charting is FILE12. FILE12 will be . . . the input to your new ODS charting code) -> /*!!!MQR!!!S<START STEP>D<Data Extract>*/ -> %LET MQRBY = RNGDTE DATEZ ; -> %MACRO MQRLIST(T=1); -> %MQRELMT(&T,RNGDTE,%STR('')) -> . . . (lots of %MQRELMT statements) -> %MEND; -> %LET FILE1=FILE12; -> %MQRFSEL(FNUM=1,PERM=NO,OUT=_CSVIN_,TYPE=SEL, -> APPEND=NO,CCC=,FFF=,SORT=YES,FILTER=N); -> %LET MQRCTTL = %NRBQUOTE(Hourly ABC Response Time); -> %MQRGCSVN(DD=&LIB); -> %MQRCSV(SASMEM=_CSVIN_,MEM=&NCSVNAME, -> . . . more statements not displayed -> RUN;
  4. Edit the new member, sharedprefix.MICS.SOURCE(ABCWRTS) that was created in Step 3. Delete any lines that are prefixed with a '->' sign.
  5. Enter the MWF - MICS Workstation Facility, and select option 2 for MICF, then option 2 for Database Inquiries:
    ----------------------------- Database Inquiries ---------------------------- Command ===> Scroll ===> CSR Catalog ===> PRIVATE (PRIVATE/SHARED) Catalog Group ===> * Create New Inquiry ===> ______ (Name) Name (subset display) ===> ________ Line Cmds: A Abstract V View Sample E Execute B Batch F Foreground S Select M Modify C Copy R Repeat D Delete
  6. Either enter the name of the new MICF inquiry or use the Insert line command. Complete the Title, Author, Catalog Group Name, and Catalog Group Title on the Insert Database Inquiry screen. Then select option 3 for User-Written Report:
    -------------------------- Insert Database Inquiry -------------------------- Option ===> Inquiry Name ===> ABCWRT Title ===> ABC Response Time Author ===> CA Catalog Group Name ===> PERFORM Title ===> Performance Analysis 1 - Direct Inquiry Define a simple, single report inquiry using an output- oriented, "single panel" approach. 2 - Structured Inquiry Define an inquiry using a process-oriented, generalized (multiple-step) approach. 3 - User-Written Report Add a user-written report to the MICF inquiry catalog.
  7. This new inquiry will have a minimum of 3 steps to execute the uploaded Q&R macro code, and call the ODS source code to produce the charts. Then select option 3 for User-Written Report:
    • External Program Invocation
    • External File Allocation
    • Independent Source Statements
  8. Select Option 3, External Program Invocation, on the User-Written Reports panel:
    ---------------------------- User-Written Report ---------------------------- Option ===> 3 Composing CA MICS Inquiry: ABCWRT - Adhoc Inquiry - Will Not Be Saved 1 - CA MICS File Allocation Specify CA MICS File, Timespan, and Database for inquiry input. 2 - Saved File Allocation Specify a saved file as an input for this inquiry. -->> 3 - External Program Invocation Specify an external program to be executed in this inquiry. 4 - External File Allocation Specify an external file to be allocated for this inquiry. 5 - Temporary Data Set Allocation Specify a temporary data set to be allocated for this inquiry. 6 - Independent Source Statements Insert SAS statements into the inquiry w/o a MICF DATA step. 7 - Execution-Time Parameter Definition Define an execution-time parameter to generate a SAS global. 8 - Data Extract Extract CA MICS data to CSV files or CA-DATACOM tables. 9 - Inquiry Function Catalog Display Display a scrollable list of MICF inquiry functions.
  9. On the External Program Invocation screen, enter the name of the sharedprefix.MICS.SOURCE member that contains the Q&R macros that was uploaded in step xx. Specify SOURCE in the
    MICS
    Library DDNAME field. Hit PF3 to return to the menu.
    ------------------------ External Program Invocation ------------------------ Command ===> Enter a ? in any data entry field for more information on valid values. Composing CA MICS Inquiry: ABCWRT - ABC Response Time Inquiry Step ===> External Program Invocation Program Name ===> ABCWRTS_ External File DDNAME ===> ________ or CA MICS Library DDNAME ===> SOURCE__ MACAUTOS - sharedprefix.MICS.MACAUTOS SOURCE - sharedprefix.MICS.SOURCE USOURCE - prefix.MICS.USER.SOURCE
  10. Select Option 4, External File Allocation. Enter 'CUSOURCE' in the DDNAME field and 'SHRUSORC' in the
    MICS
    Library field, and hit PF3 to return to the menu.
    -------------------------- External File Allocation ------------------------- Command ===> Enter a ? in any data entry field for more information on valid values. Composing CA MICS Inquiry: ABCWRT - Adhoc Inquiry - Will Not Be Saved Inquiry Step ===> External File Allocation DDNAME ===> ________ External File Data Set Name ===> ________________________________________________________ Disposition ===> ___ (OLD/SHR/MOD) or CA MICS Library ===> ________ ASM - sharedprefix.MICS.ASM GENLIB - sharedprefix.MICS.GENLIB INCLLIB - sharedprefix.MICS.INCLLIB MACAUTOS - sharedprefix.MICS.MACAUTOS MACLIB - sharedprefix.MICS.MACLIB SHRPARMS - sharedprefix.MICS.PARMS SHRUSORC - sharedprefix.MICS.USER.SOURCE SOURCE - sharedprefix.MICS.SOURCE
  11. Select Option 6, Independent Source Statements. Enter the code listed on the screen shown below. All of these statements are required. Note that this is included in the RMFODS module. This module is just a skeleton module that does a %INCLUDE SOURCE for a member with the same name as the inquiry. Specify a 'Y' for Generates PNG. If you want to also create a CSV of the charted data, specify a 'Y' for Generates CSV as well. Save the inquiry by hitting PF3.
    Note: There is no need to change the statement %INCLUDE CUSOURCE(RMFODS) ; to reference anything else. The purpose of this module is to include a sharedprefix.SOURCE member which is the same name as the inquiry. There is not need to change RMFODS to anything else. In this example, it will automatically include sharedprefix.SOURCE(ABCWRT).
    EDIT ---------------------- Independent Source Statements --------- COLUMNS 000 000 Command ===> Scroll ===> CSR Composing CA MICS Inquiry: ABCWRT - ABC Response Time Inquiry Step ===> Independent Source Statements Using Color Graphics ===> N (Y/N) Generates CSV ===> N (Y/N) Generates PNG ===> Y (Y/N) --------------------------------------------------------------------------------------- ****** ********************************* Top of Data ********************************** '''''' ODS LISTING CLOSE ; '''''' %INCLUDE CUSOURCE(ICFODSST) ; '''''' %INCLUDE CUSOURCE(RMFODS) ; '''''' *-------------------------------------------------------------------; '''''' * GENERATE: ODS CHARTS ; '''''' * FILE12 IS INPUT TO ODS CHARTS ; '''''' *-------------------------------------------------------------------; '''''' %ABCWRTA ; '''''' *; '''''' ODS LISTING ; '''''' ****** ******************************** Bottom of Data ********************************
  12. Now it's time to supply the SAS ODS code that will produce the graphical chart. Create a new member in sharedprefix.MICS.SOUCE with the same name as the inquiry. In this example, ABCWRT. In this member we will create the %ABCWRTA macro definition. Various MICS Analyzers have delivered numerous SAS ODS inquiries that produce a good variety of chart types for which to model your ODS code. Three of the most commonly composed chart types are represented here:
    1. Vertical Bar Chart
      See MICF inquiry RMFODD, "CPC and LPAR Daily Shared CP Engine Dispatch"
    2. Stacked Vertical Bar Chart
      See MICF inquiry RMFODO, "Daily zIIP Engine Use+Demand by Srv Cls"
    3. Pie Chart - RMFODQ
      See MICF inquiry RMFODQ, "zOS Daily Top 10 Srvc Classes by SYSPLEX"
    Note: Each of these inquiries are described and documented further in the reports chapter of their respective product guide.
    The contents of the ABCWRT member might look something like:
    *-------------------------------------------------; * ABCWRTA: Hourly ABC Response Time ; *-------------------------------------------------; %MACRO ABCWRTA ; ODS GRAPHICS / IMAGENAME="ABC-RESP" ; TITLE1 'PROD SYSPLEX ABC RESPONSE TIME'; TITLE2 '(HourLY) PRIME SHIFT (6:00AM - 6:00PM)'; TITLE3 '(some values are not shown on this chart)'; TITLE4 &OPTITLE; %MQRCSV(sasmem=file12); /* to generate a CSV */ %ICFEDATA(data=WORK.FILE12); PROC SGPLOT DATA=WORK.FILE12 UNIFORM=YSCALE ; by sysid date; VBAR Hour / RESPONSE=AVGEXEP1 BARWIDTH=.16 DISCRETEOFFSET=-.32; VBAR Hour / RESPONSE=AVGEXEP2 BARWIDTH=.16 DISCRETEOFFSET=-.16; VBAR Hour / RESPONSE=AVGEXEP3 BARWIDTH=.16 DISCRETEOFFSET=0 ; VBAR Hour / RESPONSE=AVGEXECP BARWIDTH=.16 DISCRETEOFFSET=.16; YAXIS GRID MINOR MIN=0 LABEL="ABC Response Time" ; XAXIS GRID LABEL="Hours of the Year" ; RUN; %MEND ABCWRTA ;