So this is how I went about it:
I first retreived a list of all objects in the information map. I only needed Data Items for what I'm doing so I could change the _all_ to dataitems next time.
proc infomaps
metauser="user id"
metapass="password"
metaserver="server"
metaport=8561;
update infomap mapname
mappath="/path to where the map is located/"
verify=no; **don't verify sources
list _all_; **list everything
The result of the above shows up in the log in Enterprise Guide. I highlight and copy it all and paste it into Excel. I insert a column to the left of the log results and auto-fill it asending to keep the row order. I then sort and/or filter by the log column to get rid of spaces and page headings the log produces. I resort (if necessary) by the row number column on the left.
I copy the contents of the log column and paste it as values into a new sheet. In the column to the right I use a trim statement for the log on the left to get rid of leading/trailing spaces. I then copy the trim column and replace the first column as Values only. If you used list _all_ and want to only get the fields you want to work with you should purge rows above and below Data Items (Data Sources, Relationships, etc.)
For Data Items there are 8 fields so I made 8 columns to the right.
Data item name: |
ID: |
Folder: |
Description: |
Expression: |
Expression type: |
Classification: |
Format: |
The structure is consistent all the way through the Data Items I used some string maniuplation to populate cells relative to the Data Item Name:
The first cell under the newly added Data Item Name column looks at the log line and if it starts with Data item name then pull everything after "Data item name:" (the length of the phrase with a space after is 16)
=IF(LEFT($B2,15)="Data item name:",RIGHT(B2,LEN(B2)-16),"")
The next column will have ID: and that will have a very similar formula but will look at the next row for its data:
=IF(LEFT($B2,15)="Data item name:",RIGHT(B3,LEN(B3)-4),"")
Continue the pattern for the others:
=IF(LEFT($B2,15)="Data item name:",RIGHT(B4,LEN(B4)-8),"")
=IF(LEFT($B2,15)="Data item name:",RIGHT(B5,LEN(B5)-13),"")
=IF(LEFT($B2,15)="Data item name:",RIGHT($B6,LEN($B6)-12),"")
=IF(LEFT($B2,15)="Data item name:",RIGHT($B7,LEN($B7)-17),"")
=IF(LEFT($B2,15)="Data item name:",RIGHT($B8,LEN($B8)-16),"")
=IF(LEFT($B2,15)="Data item name:",RIGHT($B9,LEN($B9)-8),"")
Copy this row all they down. I then copied the three columns to a new sheet and filtered for blanks and removed them. Then unfiltered the list and I am left with all of the data items that I can search through.
In my case I filtered for the table I know the infomap has recently been connected to and get the IDs of the fields. I can then query them. I create a temporary work table to help with performance:
PROC SQL;
CREATE TABLE WORK.PC_MAIL_OUT_TEST AS (
SELECT
'Address Line 1_2'N,
'Address Line 2_2'N,
etc..
;
Then I can query my temporary table:
proc sql;
select 'Postal Cd_10'N, count(*)
from work.PC_MAIL_OUT_TEST
group by 1
order by 1;
I can also compare results of that against the database by using/creating a libref to the source.
Hope you find this helpful. It was a fun exercise.