Example: Using the INFOMAPS Procedure and the Information Maps Engine |
Once the tables are registered in the metadata server, you can create a new information map. The INFOMAPS procedure inserts multiple data sources and data items, inserts relationships to join the tables, inserts four filters, and then saves the new information map.
The following code creates the new information map:
/* Create a new information map using the INFOMAPS procedure. */ proc infomaps mappath="&infomap_path"; /* Open a new information map. */ new infomap "Employee Info" auto_replace=yes; /* Insert a data source and three data items using the COLUMNS= option. */ insert datasource sasserver="SASMain" table="HR".empinfo columns=("JobCode" "LOCATION" "DIVISION") id="Empinfo"; /* Insert a data item based on a physical column. Because the ID= option */ /* is not specified, a note with its ID value will print in the SAS log. */ insert dataitem column="Empinfo".idnum classification=category; /* Insert a data item with an expression. */ insert dataitem expression="SUBSTRN(<<root.Jobcode>>, 1, 3)" type=character name="Department Code" id="Dept_code"; /* Insert a second data source, plus a data item into the */ /* current information map. */ insert datasource sasserver="SASMain" table="HR".jobcodes columns=( "TITLE" ) id="Jobcodes"; /* Change the data item to a measure so that you can use it in computations */ /* and analytical expressions. Set the default aggregation to Count. */ update dataitem "Title" aggregation=COUNT classification=MEASURE; /* Insert a third data source into the current information map. */ insert datasource sasserver="SASMain" table="HR".salary id="Salary"; /* Add joins between the tables. */ insert relationship left_table="Empinfo" right_table="Jobcodes" join=inner condition="(<<Empinfo.JOBCODE>>=<<Jobcodes.JOBCODE>>)"; insert relationship left_table="Empinfo" right_table="Salary" join=inner condition="(<<Empinfo.IDNUM>>=<<Salary.IDNUM>>)"; /* Insert a folder and additional business items. */ insert folder "Salary Info"; insert dataitem column="Salary".salary name="Annual Salary" folder="Salary Info"; /* Insert a data item that contains an expression */ insert dataitem expression="<<Salary.SALARY>>/12" type=numeric name="Monthly Salary" folder="Salary Info"; insert dataitem column="Salary".enddate folder="Salary Info"; /* Insert filters. */ insert filter "Status is Current" condition="<<root.Enddate>> IS NULL" folder="Salary Info"; insert filter "Education and Publications" condition='SUBSTRN(<<root.Jobcode>>, 1, 3) IN ("EDU","PUB")' desc="Employees in Education and Publications"; insert filter "Host Systems Development" condition='<<root.Division>>="HOST SYSTEMS DEVELOPMENT" ' desc="Employees in Host Systems Development"; insert filter "Cary HQ" condition='<<root.Location>>="Cary" ' desc="Located in Cary, North Carolina HQ"; /* List the key properties of business data in the current information map. */ list; /* Save the information map. */ save; /* End the INFOMAPS procedure. */ quit;
Note: If you run the INFOMAPS procedure code more than once, your output will be different from what is shown.
Log for the INFOMAPS Procedure
11 /* Create a new information map using the INFOMAPS procedure. */ 12 proc infomaps mappath="&infomap_path"; 13 /* Open a new information map. */ 14 new infomap "Employee Info" 15 auto_replace=yes; 16 17 /* Insert a data source and three data items using the COLUMNS= option. */ 18 insert datasource sasserver="SASMain" 19 table="HR".empinfo 20 columns=("JobCode" "LOCATION" "DIVISION") 21 id="Empinfo"; 22 23 /* Insert a data item based on a physical column. Because the ID= option */ 24 /* is not specified, a note with its ID value will print in the SAS log. */ 25 insert dataitem column="Empinfo".idnum classification=category; NOTE: A data item was inserted for the physical column Empinfo.IDNUM. The data item's ID is "Identification Number". 26 27 /* Insert a data item with an expression. */ 28 insert dataitem expression="SUBSTRN(<<root.Jobcode>>, 1, 3)" 29 type=character 30 name="Department Code" 31 id="Dept_code"; 32 33 /* Insert a second data source, plus a data item into the */ 34 /* current information map. */ 35 insert datasource sasserver="SASMain" 36 table="HR".jobcodes 37 columns=( "TITLE" ) 38 id="Jobcodes"; 39 40 /* Change the data item to a measure so that you can use it in computations */ 41 /* and analytical expressions. Set the default aggregation to Count. */ 42 update dataitem "Title" aggregation=COUNT classification=MEASURE; 43 44 /* Insert a third data source into the current information map. */ 45 insert datasource sasserver="SASMain" 46 table="HR".salary 47 id="Salary"; 48 49 /* Add joins between the tables. */ 50 insert relationship 51 left_table="Empinfo" 52 right_table="Jobcodes" 53 join=inner 54 condition="(<<Empinfo.JOBCODE>>=<<Jobcodes.JOBCODE>>)"; NOTE: A relationship between the tables "Empinfo" and "Jobcodes" has been inserted. The relationship's ID is "JOIN_10". 55 56 insert relationship 57 left_table="Empinfo" 58 right_table="Salary" 59 join=inner 60 condition="(<<Empinfo.IDNUM>>=<<Salary.IDNUM>>)"; NOTE: A relationship between the tables "Empinfo" and "Salary" has been inserted. The relationship's ID is "JOIN_11". 61 62 63 /* Insert a folder and additional business items. */ 64 insert folder "Salary Info"; 65 66 insert dataitem column="Salary".salary 67 name="Annual Salary" folder="Salary Info"; 68 69 /* Insert a data item that contains an expression */ 70 insert dataitem expression="<<Salary.SALARY>>/12" type=numeric 71 name="Monthly Salary" folder="Salary Info"; 72 73 insert dataitem column="Salary".enddate folder="Salary Info"; NOTE: A data item was inserted for the physical column Salary.ENDDATE. The data item's ID is "Enddate". 74
75 /* Insert filters. */ 76 insert filter "Status is Current" 77 condition="<<root.Enddate>> IS NULL" folder="Salary Info"; 78 79 insert filter "Education and Publications" 80 condition='SUBSTRN(<<root.Jobcode>>, 1, 3) IN ("EDU","PUB")' 81 desc="Employees in Education and Publications"; 82 83 insert filter "Host Systems Development" 84 condition='<<root.Division>>="HOST SYSTEMS DEVELOPMENT" ' 85 desc="Employees in Host Systems Development"; 86 87 insert filter "Cary HQ" 88 condition='<<root.Location>>="Cary" ' 89 desc="Located in Cary, North Carolina HQ"; 90 91 /* List the key properties of business data in the current information map. */ 92 list; Total datasources: 3 Data source: HR.EMPINFO ID: Empinfo Name: EMPINFO Description: Data source: HR.JOBCODES ID: Jobcodes Name: JOBCODES Description: Data source: HR.SALARY ID: Salary Name: SALARY Description: Total data items: 9 Data item name: Annual Salary ID: Annual Salary Folder: /Salary Info Description: Physical column SALARY Expression: <<Salary.Salary>> Expression type: NUMERIC Classification: MEASURE Format: DOLLAR12. Default aggregation: Sum Data item name: Department Code ID: Dept_code Folder: / Description: Expression: SUBSTRN(<<root.Jobcode>>, 1, 3) Expression type: CHARACTER Classification: CATEGORY Format:
Data item name: Division ID: Division Folder: / Description: Physical column DIVISION Expression: <<Empinfo.DIVISION>> Expression type: CHARACTER Classification: CATEGORY Format: Data item name: Enddate ID: Enddate Folder: /Salary Info Description: Physical column ENDDATE Expression: <<Salary.ENDDATE>> Expression type: DATE Classification: CATEGORY Format: DATE9. Data item name: Identification Number ID: Identification Number Folder: / Description: Physical column IDNUM Expression: <<Empinfo.Identification Number>> Expression type: NUMERIC Classification: CATEGORY Format: SSN11. Data item name: Jobcode ID: Jobcode Folder: / Description: Physical column JOBCODE Expression: <<Empinfo.JOBCODE>> Expression type: CHARACTER Classification: CATEGORY Format: Data item name: Location ID: Location Folder: / Description: Physical column LOCATION Expression: <<Empinfo.LOCATION>> Expression type: CHARACTER Classification: CATEGORY Format:
Data item name: Monthly Salary ID: Monthly Salary Folder: /Salary Info Description: Expression: <<Salary.Salary>>/12 Expression type: NUMERIC Classification: CATEGORY Format: DOLLAR12. Data item name: Title ID: Title Folder: / Description: Physical column TITLE Expression: <<Jobcodes.TITLE>> Expression type: CHARACTER Classification: MEASURE Format: BEST12. Default aggregation: Count Total filters: 4 Filter name: Cary HQ ID: Cary HQ Folder: / Description: Located in Cary, North Carolina HQ Expression: <<root.Location>>="Cary" Filter name: Education and Publications ID: Education and Publications Folder: / Description: Employees in Education and Publications Expression: SUBSTRN(<<root.Jobcode>>, 1, 3) IN ("EDU","PUB") Filter name: Host Systems Development ID: Host Systems Development Folder: / Description: Employees in Host Systems Development Expression: <<root.Division>>="HOST SYSTEMS DEVELOPMENT" Filter name: Status is Current ID: Status is Current Folder: /Salary Info Description: Expression: <<root.Enddate>> IS NULL Total Relationships: 2 Relationship ID: JOIN_10 Left table: HR.EMPINFO Right table: HR.JOBCODES Cardinality: UNKNOWN Join type: INNER Join expression: (<<Empinfo.JOBCODE>>=<<Jobcodes.JOBCODE>>) Relationship ID: JOIN_11 Left table: HR.EMPINFO Right table: HR.SALARY Cardinality: UNKNOWN Join type: INNER Join expression: (<<Empinfo.Identification Number>>=<<Salary.Identification Number>>) 93 94 /* Save the information map. */ 95 save; NOTE: The information map "Employee Info" has been saved in the folder "/Shared Data". 96 97 /* End the INFOMAPS procedure. */ 98 quit; NOTE: PROCEDURE INFOMAPS used (Total process time): real time 30.17 seconds cpu time 0.09 second
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.