topic Re: I want to list all libraries used per job in DI studio in SAS Data Management

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

Filipvdr — Thu, 14 Aug 2014 07:30:18 GMT

Hello everyone,

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

Regards,

Filip

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

LinusH — Thu, 14 Aug 2014 08:02:09 GMT

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.

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

Kurt_Bremser — Thu, 14 Aug 2014 10:06:46 GMT

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.

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

jakarman — Thu, 14 Aug 2014 11:05:11 GMT

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

Patrick — Thu, 14 Aug 2014 11:45:47 GMT

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"

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

LinusH — Fri, 15 Aug 2014 11:49:53 GMT

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.

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

Patrick — Sat, 16 Aug 2014 02:00:56 GMT

What about pre-assigned libraries?

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

jakarman — Sat, 16 Aug 2014 04:55:24 GMT

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.

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

Filipvdr — Mon, 18 Aug 2014 10:13:11 GMT

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.

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

Patrick — Mon, 18 Aug 2014 10:21:12 GMT

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.

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

Filipvdr — Mon, 18 Aug 2014 10:22:20 GMT

Im trying to find the libraries with the index function

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

Patrick — Mon, 18 Aug 2014 10:24:00 GMT

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.

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

jakarman — Mon, 18 Aug 2014 10:30:49 GMT

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.

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

Filipvdr — Mon, 18 Aug 2014 11:23:17 GMT

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

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

Patrick — Mon, 18 Aug 2014 11:42:34 GMT

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).

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

jakarman — Mon, 18 Aug 2014 12:00:28 GMT

So what you want is an Impact analyses / reverse impact analyses for all tables?

SAS(R) Data Integration Studio 4.4: User's Guide being enhanced SAS(R) Data Integration Studio 4.7: User's Guide

That can be done analyzing the metadata as Patrick suggested. The metdata is rather a complicated structure. SAS(R) 9.4 Metadata Model: Reference (just the tables, I am missing associations to transforms).

Your method of brute force code analyzing is more easy to understand. The list tables-libraries must be not the biggest problem.

It has the disadvantage you are needing to recognize sure a library-table combination found in some code/dataset. Is this the question?
Having two tables with all information it will become a normal table-lookup -reporting question.

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

Filipvdr — Tue, 19 Aug 2014 08:04:26 GMT

Thanks for your replies.

I have tried your suggestion Patrick but it seems it throws out the table does not include the big parts which are commented.

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

Patrick — Tue, 19 Aug 2014 09:51:45 GMT

DI 4.21 is an older version but I would have thought that DIS already generated such a header in this version. Do you have it in your code and you're simply not reading it into your data? Or what exactly is the problem you're facing?

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

LinusH — Tue, 19 Aug 2014 10:17:36 GMT

Perhaps we are back to my original suggestion to use SAS Code Analyzer (PROC SCAPROC)....?

It is available in 9.2.

Base SAS(R) 9.2 Procedures Guide

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

jakarman — Tue, 19 Aug 2014 10:33:26 GMT

Linus I always placed SCAPROC for optimizing code in smaller parts to do parallel execution. DI is generating code. I do not see a match.
Still it could be an option as Filipvdr did explain his ultimate goal. At the moment it looks to be an inventarization / impact analyses.

For the (statistic) code analyses.

The code generations is influenced by options in DI. I am not sure whether those descriptive headers are possible.
Filipvdr/Patrcik on the track.

When not, more code must be read with some assumptions on naming conventions. When there are a limited set transformations used it could be workable. When a lot of code has changed by handcraft. It is a battle of outsmarting coders work.