SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Regular Contributor
Posts: 236

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

Hello everyone,

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

Regards,

Filip

Super User
Posts: 5,429

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

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.

Data never sleeps
Super User
Posts: 5,429

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

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.

Data never sleeps
Respected Advisor
Posts: 4,173

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

What about pre-assigned libraries?

Trusted Advisor
Posts: 3,214

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

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?

---->-- ja karman --<-----
Super User
Posts: 7,782

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 3,214

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

More questions as answers:

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

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

---->-- ja karman --<-----
Respected Advisor
Posts: 4,173

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

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"

Capture.PNG

Regular Contributor
Posts: 236

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

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.

Respected Advisor
Posts: 4,173

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

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.

Regular Contributor
Posts: 236

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

Im trying to find the libraries with the index function Smiley Wink

Respected Advisor
Posts: 4,173

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

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.

Trusted Advisor
Posts: 3,214

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

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.

---->-- ja karman --<-----
Regular Contributor
Posts: 236

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

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

Respected Advisor
Posts: 4,173

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

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

Ask a Question
Discussion stats
  • 28 replies
  • 1493 views
  • 2 likes
  • 7 in conversation