Getting all Data Item queries in SAS Information Map Studio

Eitan123 — Mon, 24 May 2021 13:47:05 GMT

Hey,

My SAS version is 6.6.
In SAS Information Map Studio (version 4.4_M7), I have an information map with hundreds of data items in it.

Is there any way to get all the queries of these items in one file so I can CTRL+F?
It seems that the search options in Information Map Studio are quite limited.

Regards,

Eitan

Re: Getting all Data Item queries in SAS Information Map Studio

ChrisHemedinger — Tue, 25 May 2021 12:46:30 GMT

Hi @Eitan123 ,

Your SAS version is likely 9.x, perhaps 9.3, and I assume you're using SAS Web Report Studio and/or other SAS solutions that rely on information maps. Information Maps are simply a business view of data that make it easier to abstract the details of database access from the end user.

You might find the INFOMAPS libname engine useful for reporting on data items and filters. See example in this blog post. Code below.

/* Specify your metadata folder here. This is the folder that */ /* contains the information map definitions */ %let folder = /Shared Data/Maps; /* Specify the name of the information map for which you want the */ /* detailed report. */ %let mapName = Cars; /* SYSECHO: when this program is run within SAS Enterprise Guide, */ /* the sysecho statements show more detailed information within */ /* the task status list. */ sysecho "Assigning library for information map"; /* NOTE: when using this LIBNAME statement in a batch environment, */ /* you might need to add metadata host and credentials information. */ /* When running from SAS Enterprise Guide, it should not be */ /* necessary; your metadata identity will be used automatically. */ /* Note that the report will contain only those Information Maps */ /* for which you have Read access permissions. */ libname imaps infomaps mappath="&folder" aggregate=yes /* use already-established metadata connection */ metacredentials=no /* uncomment the following if running in batch/DMS */ /***************** host=metadatahost.com port=8561 user=yourId pw="yourPassword" ******************/ PRESERVE_MAP_NAMES=YES; footnote; /* Report of information maps in the selected folder */ proc print data=sashelp.vinfomp noobs label; var mapname description; label mapname = "Name" description = "Description"; title "Information Maps in &folder"; run; title; proc sql noprint; sysecho "Discovering information map member name"; SELECT imap.memname into :_im_memname from dictionary.infomaps as imap where imap.mapname="%trim(%superq(mapName))"; sysecho "Gathering information map data items"; create table ITEMS as SELECT d.dataitemname, d.name, d.id, d.path, d.description, c.label, d.class, c.type, c.length, c.format, c.informat, c.npos FROM dictionary.dataitems as d, dictionary.columns as c WHERE (d.libname=c.libname and d.memname=c.memname and c.name=d.name) and (d.libname ="IMAPS" and d.memname="%trim(%superq(_im_memname))") and (d.isusable="YES") ORDER BY c.npos; sysecho "Gathering information map filters"; create table FILTERS as SELECT f.filtername, f.name, f.id, f.path, f.description, f.promptusage, f.usagepromptid FROM dictionary.filters as f WHERE libname ="IMAPS" and memname="%trim(%superq(_im_memname))"; sysecho "Gathering information map prompts"; create table scratch_ALLPROMPTS as SELECT p.id, p.promptname, p.name, p.description, p.type, p.dependentpid, x.order FROM dictionary.prompts as p, dictionary.promptsxml as x WHERE p.libname ="IMAPS" and p.memname="%trim(%superq(_im_memname))" and (p.id = x.id) and (p.memname=x.memname) ORDER BY p.id, x.order ASC; create table PROMPTS as SELECT f.name as filterid, p.id, p.dependentpid, p.promptname, p.name, p.description, p.type, p.order FROM scratch_ALLPROMPTS as p, FILTERS as f WHERE (f.usagepromptid = p.id) ORDER BY f.name, p.id, p.order ASC; create table STPPROMPTS as SELECT p.id, p.promptname, p.name, p.description, p.type, p.order FROM scratch_ALLPROMPTS as p FULL JOIN PROMPTS as fp on (p.id = fp.id) WHERE fp.id IS MISSING; drop table scratch_ALLPROMPTS; quit; /* Report on Data items in a single Information Map */ proc report data=items nowd; title "Data items in &folder./&mapName"; column path dataitemname name description class format; define path / group 'Path' missing; define dataitemname / group 'Display name' missing; compute dataitemname; if dataitemname ne ' ' then hold1=dataitemname; if dataitemname eq ' ' then dataitemname=hold1; endcomp; define name / group 'Syntax Name' missing; compute name; if name ne ' ' then hold2=name; if name eq ' ' then name=hold2; endcomp; define description / group 'Description' missing; compute description; if description ne ' ' then hold3=description; if description eq ' ' then description=hold3; endcomp; define class / group 'Type' missing; compute class; if class ne ' ' then hold4=class; if class eq ' ' then class=hold4; endcomp; define format / group 'Format' missing; compute format; if format ne ' ' then hold5=format; if format eq ' ' then format=hold5; endcomp; run; quit; proc report data=filters nowd; title "Filters in &folder./&mapName"; column path filtername name description promptusage; define path / group 'Path' missing; define filtername / group 'Display name' missing; compute filtername; if filtername ne ' ' then hold2=filtername; if filtername eq ' ' then filtername=hold2; endcomp; define name / group 'Syntax name' missing; compute name; if name ne ' ' then hold3=name; if name eq ' ' then name=hold3; endcomp; define description / group 'Description' missing; compute description; if description ne ' ' then hold4=description; if description eq ' ' then description=hold4; endcomp; define promptusage / group 'Uses prompts?' missing; compute promptusage; if promptusage ne ' ' then hold5=promptusage; if promptusage eq ' ' then promptusage=hold5; endcomp; run; quit; proc print data=stpprompts noobs label; title "Stored process prompts in &folder./&mapName"; var promptname description; label promptname="Prompt name" description="Description"; run; title; /* clear the libname when complete */ libname imaps clear;

Re: Getting all Data Item queries in SAS Information Map Studio

Eitan123 — Wed, 09 Jun 2021 07:30:32 GMT

Hey @ChrisHemedinger ,

Firstly, Thanks!
I ran your code with my specific details (folder+map information) and I have the following coloumns for my data item in results:

Path	Display name	Syntax Name	Description	Type	Format

However, there's one column missing - the expression of each data item. Could you please tell me from which table+column I can find this information?

Regards,
Eitan

Re: Getting all Data Item queries in SAS Information Map Studio

ChrisHemedinger — Wed, 09 Jun 2021 12:06:31 GMT

I'm not sure the expression/calculation is available using the INFOMAPS engine. See the complete list of dictionary table elements in the documentation.

Keep in mind that Information Maps are--by design--opaque to the end user, meant to obscure the actual physical data and hide the details/complexity. Information Map Studio is the only application that can yield the full details about all of the definitions.

Re: Getting all Data Item queries in SAS Information Map Studio

Madelyn_SAS — Tue, 21 Sep 2021 14:12:48 GMT

You can see the expression by using File > Print. The format is not as nice as using Chris' code though.

Re: Getting all Data Item queries in SAS Information Map Studio

Eitan123 — Wed, 22 Sep 2021 12:08:58 GMT

What is File > Print?

I'm no SAS programmer 😞

Re: Getting all Data Item queries in SAS Information Map Studio

Madelyn_SAS — Wed, 22 Sep 2021 13:19:24 GMT

In SAS Information Map Studio, select the File menu and then the Print option.

topic Re: Getting all Data Item queries in SAS Information Map Studio in New SAS User

Getting all Data Item queries in SAS Information Map Studio

Re: Getting all Data Item queries in SAS Information Map Studio

Re: Getting all Data Item queries in SAS Information Map Studio

Re: Getting all Data Item queries in SAS Information Map Studio

Re: Getting all Data Item queries in SAS Information Map Studio

Re: Getting all Data Item queries in SAS Information Map Studio

Re: Getting all Data Item queries in SAS Information Map Studio