Manipulating MICS Data
MICS
DataMICF provides you with a wide variety of data manipulation facilities. The use of each of these facilities is optional.
micsrm140
One of these facilities, General Data Selection, is used frequently enough that it has been made an option on the Structured Inquiry menu. The remaining data manipulation facilities are on the Data Manipulation menu.
Data Manipulation Menu
----------------------------- Data Manipulation ----------------------------- Option ===> ComposingMICSInquiry: SI1 - Sample Structured Inquiry- 1 -MICSFile Selection SpecifyMICSfile, timespan, and database for inquiry input. 2 - Data Extract ExtractMICSdata to CSV files or keyword keyref="dat"/> tables. 3 - Color Graphics Structured inquiry functions for color graphics outputs. 4 - Printed Reports Structured inquiry functions for printed reports. 5 - Printer Graphics Structured inquiry functions for printer graphics outputs. 6 - Statistical Analysis Structured inquiry functions for statistical analysis. 7 - Data Manipulation Subset, summarize, and/or enhance the input file. 8 - Full Screen SAS Processing Process the input file with the SAS Full Screen Product (FSP). 9 - Inquiry Function Catalog Display Display a scrollable list of MICF inquiry functions.
Subsetting
MICS
FilesTo subset, a
MICS
file is to select only some of the observations from the input file. The three standard ways to subset a MICS
file in MICF are:- Common Data Selection
- General Data Selection
- Execution-time Data Selection
Common Data Selection
Common data selection is used to subset the input file that is based on date, time, system, zone, and record numbers. You can use combinations of these factors in the subsetting process.
If you select the Common Data Selection option from the Structured Inquiry menu, the Common Data Selection panel displays.
Common Data Selection Panel
--------------------------- Common Data Selection --------------------------- Command ===> Enter a ? in any data entry field for more information on valid values. ComposingMICSInquiry: SSI - Sample Structured Inquiry Inquiry Step ===> Common Data Selection Input File ===> FILE1 (From: DAYS TSU ) Output File ===> FILE1 Time frame ===> ________ (CURRENT/PREVIOUS) ===> _____ (DAY/WEEK/MONTH) Date ===> _______ to ===> _______ (ddmonyy) Time ===> ___________ to ===> ___________ (hh:mm:ss.hs) Year ===> __ to ===> __ (Last Two Digits) Month ===> __ to ===> __ (1-13) Day ===> __ to ===> __ (1-31) Week ===> __ to ===> __ (1-53) Hour ===> __ to ===> __ (0-23) Zone ===> _ _ _ _ _ _ _ _ _ (1-9) Dayname ===> ___ ___ ___ ___ ___ ___ ___ (Sun, Mon, Tue, ..) SYSID ===> ____ ____ ____ ____ ____ ____ (1-4 Character System-id) Start After ===> ________ (Skip the first N records in the file) Stop After ===> ________ (Stop processing file after N records) Skip Factor ===> ________ (Select every Nth record in the file) -------------------------------------------------------------------------------
The Common Data Selection function provides the following capabilities:
- Select data for the previous day, current or previous week, or current or previous month. This data is the "time frame selection" facility, which provides dynamic date-based selection for file subsetting. The data that is selected through common data selection are a subset of the data that are specified after the timespan prompt on the file selection panel.
- Select data based on date, time, year, month, day, week, hour, or day of the week (Dayname), or both.
- Select data based on Zone.
- Select data based on system (SYSID).
- Select data following the nth record, select data until the nth record, or select every nth record, or both. For example, start, stop, and skip.
If multiple selection criteria are specified, a file observation must meet ALL of the specifications to be kept. If only one specification is missed, the record is dropped.
As with other MICF panels, the Inquiry Step defaults to the inquiry function name, but you have the option of providing a more descriptive name. This name that appears as the inquiry step name on the MICF Inquiry Step Display panel.
The input file defaults to the output file of the previous inquiry step. The output file defaults to the input file. You can override these values by entering appropriate file names after the corresponding prompts. The output file names must follow the rules that are discussed in Completing the
MICS
File Selection Panel.Sample of a Completed Common Data Selection Panel is an example of a Common Data Selection panel that is completed to select the data from the week before the one on which the inquiry is run (default). Since there are also two entries after the SYSID prompt, only data from the PTSO or ETSO systems will be used.
Sample of a Completed Common Data Selection Panel
--------------------------- Common Data Selection --------------------------- Command ===> ComposingMICSInquiry: SSI - Sample Structured Inquiry Inquiry Step ===> Common Data Selection Input File ===> FILE1 (From: DAYS TSU ) Output File ===> FILE1 Time frame ===> PREVIOUS (CURRENT/PREVIOUS) ===> WEEK (WEEK/MONTH) Date ===> _______ to ===> _______ (ddmonyy) Time ===> ___________ to ===> ___________ (hh:mm:ss:hs) Year ===> __ to ===> __ (Last Two Digits) Month ===> __ to ===> __ (1-13) Day ===> __ to ===> __ (1-31) Week ===> __ to ===> __ (1-53) Hour ===> __ to ===> __ (0-23) Zone ===> _ _ _ _ _ _ _ _ _ (1-9) Dayname ===> ___ ___ ___ ___ ___ ___ ___ Not In File SYSID ===> PTSO ETSO ____ ____ ____ ____ (1-4 Character System-id) Start After ===> ________ (Skip the first N records in the file) Stop After ===> ________ (Stop processing file after N records) Skip Factor ===> ________ (Select every Nth record in the file) -------------------------------------------------------------------------------
General Data Selection
The General Data Selection panel lets you extend the Common Data Selection process to any
MICS data element
in the file that you are analyzing. A full range of Boolean operators is allowed. The General Data Selection option allows selection that is based on data element values being equal to, greater than, greater than or equal to, less than, less than or equal to, or not equal to a specified value. You can specify compound conditions that are connected by AND or OR. In addition, you can restrict the comparisons to the leading characters of character-valued variables.The following figure shows a sample of a Completed General Data Selection Panel.
Sample of a Completed General Data Selection Panel
-------------------------- General Data Selection -------------------------- Command ===> ComposingMICSInquiry: PGMSTY - Engineering Program Analysis Inquiry Step ===> General Data Selection - Select Expensive Eng. Programs Input File ===> FILE2 (From: DETAIL PGM ) Output File ===> FILE2 Element Op. Value Connector -------- --- --------------------------------------------------- --- PROGRAM_ EQ: 'ENG' OR_ PROGRAM_ GE_ 'E340' AND PROGRAM_ LT_ 'E710' ;__ PGMCOST GT_ 5000 ;__ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ ________ ___ ___ -------------------------------------------------------------------------------
Complete the inquiry step, input file, and output file fields as in other MICF panels. The remaining fields are each discussed separately here:
Element
Complete the element field with the name or names of the data elements whose values are to be used in the comparisons.
Op.
The op. field is the operator field. Complete it with the logical operator to be used in the comparison. Valid operators are:
EQ
Equal to (the default operator)
GT
Greater than
GE
Greater than or equal to
LT
Less than
LE
Less than or equal to
NE
Not equal to
For character data elements, the operator can be followed by a colon (:) to indicate that the comparison is to be limited to leading characters. For example, this general data selection entry selects user identifiers whose values start with the character string XYZ:
USER EQ: 'XYZ'
Value
Value is the conditional test value. This value can be a literal, an input file data element, or a SAS global variable. Value specifications are translated to uppercase unless you have specified (in your User Profile Parameters) that all value type fields can contain lowercase characters. Literal values are checked for compatibility with the data element type and length. For example, values for numeric data elements must be numeric. Character literals must be enclosed in single quotes. Date and time literals are converted to valid SAS literal formats. These formats are
not
entered with quotes and type indicators. For example, 10:00:00 and '10:00:00'T are both valid specifications for ten hours for a time data element.Connector
The connector can be AND, OR, or a semicolon (;). A semicolon (the default) terminates the list of conditions in a general data selection statement. Compound conditions are evaluated from top to bottom; however, conditions that are connected with AND are processed before those connected with OR. For example, the following condition is true if B2=Y2 and C3=Z3 are true:
A1 EQ X1 OR B2 EQ Y2 AND C3 EQ Z3 ;
The condition is also true if A1=X1 is true. If multiple general data selection statements ending with a semicolon are included, the record is dropped if ANY of the statements is false. For example, in Sample of a Completed General Data Selection Panel, if PGMCOST is less than 5000, the record is dropped even if it meets the conditions on the first four condition lines on the panel. Conversely, if the first general data selection statement fails (the statement consisting of the first four conditions), the record is dropped even if PGMCOST is greater than 5000.
Execution-Time Data Selection
You can also specify execution-time data selection with a flexible set of Boolean operations by selecting the Execution-Time Data Selection option (option 3) from the Data Manipulation panel. The following figure is a sample of a blank Execution-Time Data Selection panel.
Execution-Time Data Selection Panel
----------------------- Execution-Time Data Selection ----------------------- Command ===> Enter a ? in any data entry field for more information on valid values. ComposingMICSInquiry: TSOWKP - Weekly TSO Performance Report Inquiry Step ===> Execution-Time Data Selection Input File ===> FILE1 (From: WEEKS TSO ) Output File ===> FILE1 Selection Element ===> ________ Data Entry Instructions to Be Displayed at Execution Time (the user prompt) ===> _________________________________________________________________________ Selection Selection Value Status Operator Default Value Connector (R/O/N) --------- ------------------------------------------ --------- ------------ ===> ___ __________________________________________ ===> ___ ===> O ===> ___ __________________________________________ ===> N Alternative Operator When Only One Value Is Specified ===> ___ -------------------------------------------------------------------------------
The following sample of a Completed Execution-Time Data Selection Panel prompts you to specify the zone or range of zones to be reported. The defaults are 1 (the lower bound) and 4 (the upper bound).
Sample of a Completed Execution-Time Data Selection Panel
----------------------- Execution-Time Data Selection ----------------------- Command ===> ComposingMICSInquiry: TSOWKP - Weekly TSO Performance Report Inquiry Step ===> Execution-Time Data Selection - Select Zones Input File ===> FILE1 (From: WEEKS TSO ) Output File ===> FILE1 Selection Element ===> ZONE Data Entry Instructions To Be Displayed At Execution Time (the user prompt) ===> Specify the Zone or Range of Zones to be reported (values from 1-9). Selection Selection Value Status Operator Default Value Connector (R/O/N) --------- ------------------------------------------ --------- ------------ ===> GE 1 ===> AND ===> R ===> LE 4 ===> O Alternative Operator When Only One Value is Specified ===> EQ -------------------------------------------------------------------------------
The Selection Element is the data element that is used for execution-time selection processing. Enter a question mark (?) in the Selection Element field for a list of valid element names.
Note:
When Selection Element is YEAR, data values are automatically converted to 4-digit year format in the generated SAS statements. The displayed data value is not altered. With this special feature, you can enter 2-digit (for example, 01), 3-digit (101), or 4-digit (2001) values for execution-time data selection against the MICS
YEAR element.The instructions that you enter following the prompt "The Data Entry Instructions to Be Displayed at Execution Time" are displayed when the inquiry is executed. These instructions can be up to 73 characters long. Clearly indicate what should be entered. When it is practical, list the valid values.
The next section of the panel defines the data selection operation that allows both specific value and range selection. For example, select Zone 2 or select Zones 1 through 4.This section has two rows of parameters that define the two data entry fields that are displayed at inquiry execution. The first row defines the data entry field for specific value selection. This row is also the low value for range selection. The second row defines the data entry field for the range selection high value. Parameter contents vary depending upon whether the corresponding data entry field is required, optional, or not allowed (that is, must be blank). This parameter is specified in the Value Status parameter. Descriptions of each specification follow.
Selection Operator
The Selection Operator is required if Value Status is R. You can use the following conditional test operators:
EQ
Equal to (the default operator)
GT
Greater than
GE
Greater than or equal to
LT
Less than
LE
Less than or equal to
NE
Not equal to
For character data elements, the operator can be followed by a colon (:) to specify that the comparison is to be limited to leading characters. For example, EQ: select any observation for which the data element value begins with the character string specified in the corresponding default value field.
Default Value
Default Value is an optional entry. This is the default value that is displayed on the Execution-Time Parameter Specification panel. A default value is useful if it meets selection requirements for most executions of the inquiry, by saving you from having to enter the value each time. A default is also an excellent way to clarify the data entry instructions.
The value is a literal that is compatible with the data element type (if the element is numeric, then the value must be numeric) and length. MICF will convert values to valid SAS literal constructs after editing. Unlike General Data Selection, character values are
not
entered with quotes. Date and time literals are converted to valid SAS literal formats, so they do not have to be entered with quotes and type indicators. For example, 10:00:00 and '10:00:00'T are both valid specifications for ten hours for a time data element. Specifications are converted to uppercase unless you have specified that all "value" type fields can contain lowercase characters.Selection Connector
The Selection Connector is a required entry if the Value Status is R for the second specification row. That is, if two execution-time data selection values are required, specify a selection connector. The Selection Connector is the conditional test connector. Valid values are AND and OR.
Value Status
Value Status defines whether the value is Required (R), Optional (O), or Not Allowed (N).
- If R is specified, supply a value at execution time unless a default value has been specified.
- Specify O for the first data entry field only if it is valid to execute the inquiry without performing data selection against the selection element (that is, execution-time data selection is ignored if no value is specified).
- If N is specified, no value is allowed.
Alternative Operator
The Alternative Operator is an optional entry. It can have any of the values that the Selection Operator can have. This parameter replaces the selection operator for the first data entry field if the second data entry field is left blank at execution time. For example, you can select data for a specific Zone (for example, Zone 2) or alternatively select data for a range of Zones (for example, Zones 1-4). The range selection (Zones 1-4) is defined by specifying GE for the first selection operator and LE for the second selection operator. However, if you coded only the first value, all zones greater than that value would be selected (for example, ZONE GE 2). This situation is addressed by specifying EQ as the alternative operator. Then, when only one value is specified at execution time, the specific value selection is generated (for example, Zone 2) in place of the range selection.
Data Summarization
The Data Summarization panel provides space to specify 15 data elements as the file sequence keys. The file is sorted in sequence by the specified keys (from top to bottom), and then summarized to the granularity of the last data element in the list. That is, the topmost element in the list is the most significant key in the sort order and the lowest element on the list is the least significant key.
Sample of a Completed Data Summarization Panel
----------------------------- Data Summarization ---------------------------- Command ===> Enter a ? in any data entry field for more information on valid values. ModifyingMICSInquiry: SSI - Sample Structured Inquiry Inquiry Step ===> Data Summarization Input File ===> FILE1 (From: DAYS TSU ) Output File ===> FILE1 ===> SYSID ===> _ The list of data elements to the left defines how ===> USER ===> _ the file is to be summarized. Specify the data ===> YEAR ===> _ elements in the order by which the file is to be ===> MONTH ===> _ summarized, with top to bottom representing major ===> DAY ===> _ to minor order. ===> HOUR ===> _ ===> ________ ===> _ Additionally, you may control the sort order ===> ________ ===> _ within elements by specifying ascending or ===> ________ ===> _ descending (A or D) in the field to the right of ===> ________ ===> _ each element name. If the field is left blank ===> ________ ===> _ for any element, ascending is used. ===> ________ ===> _ ===> ________ ===> _ ===> ________ ===> _ ===> ________ ===> _ -------------------------------------------------------------------------------
You can display the Data Summarization panel by specifying option 2, Data Summarization, on the Data Manipulation menu.
Two data entry fields are provided for each sequence key:
- Specify the eight-character data element name in the first field. This must be the name of a data element in the input file.
- Specify a question mark (?) for a list of valid data elements.
The second data entry field is used to specify the sort sequence for that data element. Specify D to request that the file be sorted in descending order by this data element. Leave the second field blank or specify A (the default) to request ascending order for this data element.
The panel comes with the sequence of the input file. The most common scenario is to drop data elements from the end of the list to reduce the granularity of the input file for reporting purposes. You can drop elements from the top or middle of the list by simply blanking them out. Blank entries at the top or middle of the list are
not
a problem.Deriving New Data Elements
Use Data Element Derivation to add new data elements to an intermediate file of your structured inquiry. This feature is useful when you need to report a value that is not stored on the
MICS database
.The Data Element Derivation panel allows you to derive a new data element value from other input file data elements in the file. You can define the new element's name, label (long name), type (for example, alphabetic, numeric, or date), length, and output format. You can display the Data Element Derivation panel by selecting option 4, Data Element Derivation, from the Data Manipulation menu.
Sample of a Completed Data Element Derivation Panel
-------------------------- Data Element Derivation -------------------------- Command ===> ComposingMICSInquiry: BTCHWT - Batch Job Wait Time Analysis Inquiry Step ===> Data Element Derivation - Compute Avg. Wait Time/Job Input File ===> FILE2 (From: DETAIL PGM ) Output File ===> FILE2 Element Name ===> JOBAVWTM Label ===> Avg. Wait Time/Batch Job Element Type ===> T A - Alphabetic, N - Numeric, T - Time (quantity), DT - Date-Time, H - Hex, D - Date, TD - Time-of-day, M - Money Value Length ===> 11 Output Format ===> TIME11.2 Element Derivation: ===> JOBAVWTM = (JOBEXCTM - JOBACTTM) / JOBCOUNT; ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ -------------------------------------------------------------------------------
Advanced Data Manipulation
The Advanced Data Manipulation process of Structured Inquiry composition provides access to inquiry functions for:
- Defining intermediate file contents
- Coding free-form SAS statements
- Combining multiple input files to meet reporting requirements
- Inputting saved files from previously executed MICF inquiries
You can display the Advanced Data Manipulation menu by selecting option 6, Advanced Data Manipulation, from the Data Manipulation menu.
The following operations are available on the Advanced Data Manipulation menu:
- File Contents Specification lets you keep or drop data elements or modify labels and formats, or both.
- SAS System Statements let you manipulate the input file with SAS statements.
- File Concatenation/Interleaving let you combine parallel files to create a new file.
- Relational Retrieval/File Merge lets you combine unlike files to create a new, composite file.
- Data Sequencing lets you sort the input file into a new sequence.
- Independent Source Statements lets you insert your own SAS data steps and procedures. It also contains options that are used to identify the type of output the step produces. Setting these options enables MICF to create the necessary infrastructure for your output type.
- Execution-Time Parameter Definition lets you define an execution-time parameter that generates a SAS global variable. The prompt that you specify is displayed when the inquiry is executed. The parameter value that is entered at execution time is saved in a SAS global variable that you can use for data selection, derivation, or reporting, or both.
- Data Transfer Facility lets you convert a SAS file into a self-defining, transportable file that can be used to transferMICSdata between applications. Data Transfer Facility output is in comma-separated value (CSV) file format.
In addition to the CSV file, an optional Profile can be created for use in the
Broadcom
Service Assure product. This profile contains descriptive information regarding the labels, element types, and lengths of the data elements that are contained in the CSV. The profile format is based on the requirements of the Service Assure product and therefore is undocumented and subject to change.Details on the use of these operations are provided in the online tutorials.
Saved Files Facilities
Intermediate files that are created by a MICF inquiry are typically deleted when the inquiry completes execution. The Saved Files Facilities let you display the intermediate files. In addition, this facility provides you with the capability to save intermediate files created by an inquiry, and then to use them as input to later MICF inquiries.
The
MICS Capacity Planner
management application provides facilities for generating user-defined files of historical MICS
data, user data, and forecasts for capacity planning. The Saved File Facilities menu provides access to capacity planning database files so that you can use MICF's color graphics and reporting options to supplement the standard Capacity Planning Product reports and graphics.You can display the Saved Files Facilities panel by selecting option 7, Saved Files Facilities, from the Data Manipulation menu. Details on the use of the Saved File facility are provided in the online tutorial for the facility.
External File Facilities
You can use the MICF External File Facilities to extend standard MICF inquiry capabilities. You can allocate an external (non-
MICS
) program library and can invoke a user-written program for special processing or reporting. You can allocate temporary data sets or gain access to standard MICS
libraries not typically allocated for MICF inquiry execution.You can display the External File Facilities panel by selecting option 8, External File Facilities, from the Data Manipulation menu. Details on the use of the External File facility are provided in the online tutorial for the facility.