topic Re: Can you query the Information Map in SAS Customer Intelligence

Can you query the Information Map

pcapazzi — Fri, 16 Jun 2017 14:10:06 GMT

Is it possible to query the Information Map with PROC SQL?

I'd like to have a script where I can verify a field has been added and query it to ensure the relationship is set right or that there are no unexpected duplicates in other tables.

Re: Can you query the Information Map

Dmitry_Alergant — Fri, 16 Jun 2017 14:37:18 GMT

Surprisingly, it seems to be supported, at least in theory:

Using the SAS Information Maps LIBNAME Engine

 libname mymaps infomaps metauser=myUserID
                         metapass=myPassword
                         metaserver="myserver.mycompany.com"
                         metaport=8561
                         mappath="/Users/myUserID/My Folder";

I'm not aware though of anyone who has done that, so would love to hear of your use case and experience doing it this way.

Would you share?

Re: Can you query the Information Map

pcapazzi — Fri, 16 Jun 2017 18:18:11 GMT

Thanks! You got me going in the right direction.

I am able to get details of the current information map and can store it and compare when changes I request are made.

/*
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
*/

This shows the results in a log. There is an XML export function which I can explore.

But what I really was happy to see was using proc sql worked out as expected:

libname mymaps infomaps metauser=userid
metapass=password
metaserver="server"
metaport=8561
mappath="/path to infomap/";

proc datasets lib=mymaps; **libname for this path
run;
quit;

option validvarname=any; **case insensitive

proc contents data=mymaps.mapname; **put in the campaign map name
run;

proc sql ; **run a query against a data item and compare results to your source.
select count(*)
from mymaps.CampaignMap
where 'Postal Cd'n='33065'; **for field names with spaces enclose them in single quotes with an n afterwards.

Worked like a charm!

Re: Can you query the Information Map

pcapazzi — Fri, 23 Jun 2017 19:01:08 GMT

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.

Re: Can you query the Information Map

Dmitry_Alergant — Mon, 26 Jun 2017 10:52:59 GMT

Makes sense, thanks!