I want to list all libraries used per job in DI studio

[Filipvdr]

Hello everyone,

I want to list the libraries used per job. Can anyone help me?

Regards,

Filip

[LinusH]

I don't there is a out-of-the-box report for this.

One way is to dig into the wonderful world of data step metadata functions.

Another way could be (just elaborating here, not tested this myself) to parse the output from SAS Code Analyser.

[LinusH]

I came to think that suggesting the SAS Code Analyzer, it was like crossing the river for water. Parsing the deployed SAS code must be the easiest way, just check for LIBNAME in pos 1-7 and then just scan the libref name and optionally engine etc.

[Patrick]

What about pre-assigned libraries?

[jakarman]

You are right Patrick.  We did not see any reaction of the OP what he is after for.  That is why I asked to elaborate.

More questions as answers:

- static (metadata impact) or usage analyses (APM)

- DI studio version? run time environment? available loggings?

[Kurt_Bremser]

Look at the log files. The notes concerning data sets have only a few forms and can quite easily be found. Then you can parse the library names from the dataset names.

[jakarman]

More questions as answers:

- static (metadata impact) or usage analyses (APM)

- DI studio version? run time environment? available loggings?

[Patrick]

Totally agree with that there are no "out-of-the-box" reports and that you're having mainly 2 options which both will require quite a bit of coding.

1. Query SAS Metadata SAS(R) 9.4 Language Interfaces to Metadata, Second Edition

2. Parse the SAS logs

I've done both things in the past and both approaches took me quite a while to get it right.

For what you're trying to achieve I'd probably go with option 1). If you have PC SAS available then what really helped me in the past to understand the SAS Metadata Model is using "Metabrowse"

[Filipvdr]

We have SAS DI 4.21

I think static.

I have a table with all lines of code. I'm trying to loop through all the libraries and find with index if it is used. There are better ways probably.

[Patrick]

Hmm... Not sure that I understand. An index is on a table and not on a library.

Using DI Studio you should have the "create index" in the code.

[Filipvdr]

Im trying to find the libraries with the index function

[Patrick]

There won't be a libname statement for pre-assigned libraries in the code (else it would be a very simple tasks).

But... You could scan the header comments looking for all comment blocks starting with  * Target Tables: or * Source Tables: This would give you all the <libref>.<table> used.

[jakarman]

Trying to understand your question....

You are looking for which datasets have some indexes defined?

In that case you can use the contents (proc contents) or similar on the libraries. Indexes on datasets can be defined as a tuning measure. They do not need to be part of a DI code. With a external RDBMS this would be a DBA job/task.

[Filipvdr]

No: my question is the following:

I want a list of all libraries used in each job.

How I was working now:

- librariers are pre-assigned

- we have a list of all the code in one table

I make a list of all libraries available, and i will loop them one by one.

I check if the library can be found in each record of the table with the complete code

If so, i create a field with the name of the library and i set it to 1.

I hope it is more clear now

[Patrick]

If ALL libraries are pre-assigned and you're running your code on the same logical server where you're also executing your DI jobs then it's no more that hard.

1. Scan through your code and look for all comment blocks starting with * Source Tables: or * Target Tables:  (you will have to read several lines per block).

This will look as below in your code:

* Source Tables:  tableA - mylib.tableA             A5RH62SX.BI0002I7 *

*                 tableB - mylib.tableB             A5RH62SX.BI0003EL *

* Target Tables:  tableC - target.tableC            A5RH62SX.BI0002II *

*                                                                     *

* Generated on:    <datetime>                                                                                 *

In doing so you can retrieve all librefs used. Here: "mylib" and "target".

Then use "sashelp.vlibnam" and merge over column "libname" (which is the libref). This will give you all the information you're after.

Alternatively: You could use the table metadata id's and then query SAS Metadata to get the associated libraries for the table objects (eg. for A5RH62SX.BI0002I7). This would work for both pre-assigned and non pre-assigned libraries and you could also run your code under a different logical server as long as you can access the same SAS Metadata server (with my example data the repository would have the ID A5RH62SX).