BookmarkSubscribeRSS Feed
Filipvdr
Pyrite | Level 9

Hello everyone,

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

Regards,

Filip

28 REPLIES 28
LinusH
Tourmaline | Level 20

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
LinusH
Tourmaline | Level 20

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
Patrick
Opal | Level 21

What about pre-assigned libraries?

jakarman
Barite | Level 11

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 --<-----
Kurt_Bremser
Super User

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
Barite | Level 11

More questions as answers:

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

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

---->-- ja karman --<-----
Patrick
Opal | Level 21

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

Filipvdr
Pyrite | Level 9

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
Opal | Level 21

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
Pyrite | Level 9

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

Patrick
Opal | Level 21

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
Barite | Level 11

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 --<-----
Filipvdr
Pyrite | Level 9

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 28 replies
  • 3404 views
  • 2 likes
  • 7 in conversation