Finding Which program is producing a Particluar Dataset/How to locate a Dataset in a Library

[sasprofile]

Hi Friends,

I have two below Questions, please can you help with them.

Question 1

I have a Dataset in a Base SAS Library, and I don’t know
which Program is producing that Dataset.

How to find which program is producing  this particular
Dataset.

Is there any way to find that from Server or from SAS
Management console or any other way.

Question 2

And also we have so many Libraries and I want to search for
a particular Dataset in which library its located.

Instead of opening and checking each library, Is there any
easy way to search for that Dataset with a code.

Thank you in advance

[RW9]

Well, I am not familiar with EG, however in Base SAS there is the metadata tables which you can query.  In code:

proc sql;

     create table WANT as

     select     *

     from       DICTIONARY.TABLES

     where     NAME="ADATASET";

quit;

The above should return a dataset of all libraries which contain a dataset with ADATASET as the name.

For your first question, why not just do a text in file search on the network path which holds you code, i.e. find in files (Windows) files containing ADATASET.

[sasprofile]

I tried below code as an test for CARS dataset in sashelp library,but throwing error

ERROR: The following columns were not found in the contributing tables: NAME.

sql;

table test085 as select * from DICTIONARY.TABLES

NAME="CARS";

;

[Kanna]

I would use PROC CONTENTS with _ALL_ in a macro to scan each library, and output the results in a table each

The results need to be deduplicated to a single row for each table (memname) in a library, thus eliminating the details, keeping only the names.

%macro mycnts(mylib,myoutfl);

PROC CONTENTS data =&mylib._ALL_ out=&mycontents NOPRINT /* optional: */ (KEEP = LIBNAME MEMNAME) ;

RUN;

proc sort data = &mycontents nodupkey;

by libname memname;

run;

%mend;

/*Eample for executing the macro*/

%mycnts(work,lib1);

Repeat this for the rest of your libraries

Evaluate the resulting tables Lib1 lib2 libn, you can concatenate all into one.

I hope this helps.

For Question # 1 I would use a text editor, like UltraEdit, Find in Files function on the program library (hopefully there is one) to locate the table name in a program.

Pattern to use: "data lib1".

There may be admin tools that could be used, but I am not familiar.

[RW9]

Sorry, I don't have SAS open, I thought the variable was NAME.  Anyway, just drop the where and you will get the complete dataset, then you can see what the name variable is.  It could be memname.

[eroche]

I had a similar issue where we had a large compiliation of SAS programs and it was difficult to determine which code output certain datasets. I could not find an easy way to get this. The closest I got to automating it was using the SCAPROC procedure. This might help you.

https://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199752.htm

[bentleyj1]

I like eroche's SCAPROC procedure approach.    You'll have to capture the file names in your directory that holds programs into macro variables and then loop thru them to apply PROC SCAPROC.  Capture each program's procedure output to a separate text file.  Then read in the text file via a DATA step with each line as a single record and search for 'JOBSPLIT: DATASET OUTPUT SEQ <libref>.<dataset name>.DATA'.   Because the programs are 'short' it would run lickety-split.  You could write this as a macro and use macro variables for the directory to search, libref and dataset name.

Searching for the DATASET OUTPUT string would eliminate the false positives you'd might get from just scanning programs for the libref and dataset.  And you won't have to search programs for separate strings--DATA libref.dataset and CREATE TABLE libref.dataset and OUT=libref.dataset are three I thought of.:smileygrin:

Here's a link to PROC SCAPROC.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a003199745.htm

Actually, this would make a great Coder's Corner paper.

Good luck!

[Astounding]

Just to muddy the waters a bit, remember it is possible that more than one program replaces the same data set.

[bentleyj1]

This new requirement adds another step or three.   My first thought was ok, scan the logs instead of using proc scaproc but the logs themselves by default don't contain a date-time stamp on each page as does the listing so the logs themselves don't tell us the execution order.

Here's a tip for how to add start time to a log, but it would require modifying each program.  http://www.sascommunity.org/wiki/Tips:Adding_a_date_and_time_stamp_message_to_the_SAS_Log

So let's say you've used proc scaproc and identified which programs creates/replaces a table and you've got three of them.   You'll need to save those program names into macro variables (or a list that can be parsed using the scan function).  So now you scan the directory that holds the logs and use the three macro variables holding the program names to find the three logs along with their date-time stamps (hopefully they the log has the program name in it and the program wrote only one log per program--a program could rename the log or write multiple logs via proc printto.)  Then compare the date-time stamps attached to the logs and the most recent one wins.

This might be getting too long for Coder's Corner.

[Kurt_Bremser]

If your dataset is created by a job defined in SAS DI Studio, you will probably be able to find the metadata there. If not, then you can only try to find the dataset's name in either the code or in the log(s), if such exist.

In a similar situation, I look at the creation date of the file and then scan through my database of batch job runs to determine which job created a dataset.

With user created datasets, I just determine the owner with a ls -l and question her/him.

[jimbobob]

If you have Enterprise Guide, there are some add-ins that allows you to search all the server libraries at once to find a particular dataset, check out Chris Hemedinger blog site:  http://blogs.sas.com/content/sasdummy/2013/11/25/11-custom-tasks/

Also another tool if you have a lot of code buried in enterprise guide projects, try this one:

http://blogs.sas.com/content/sasdummy/2013/01/25/egp-search-tool2/

[CarthiQ]

For your second question below code will return the library name of specific dataset.

Proc sql;

     select distinct libname from sashelp.vcolumn where upcase(memname) = "DATASETS NAME";

Quit;

[sasprofile]

Thanks for your help

your code looks like it will search each and every library,but without searching every library i want to find that particular data set is located in which library.

here you are using as an example sashelp

[RW9]

Lets maybe clarify here what SASHELP contains.  It is a SAS specific library, within which resides the metadata of the system.  Part of this metadata is a list of library/dataset information which can be found in the view SASHELP.VTABLE, and the column information per library/dataset which can be found in the view SASHELP.VCOLUMN.  If you ope either of these in SAS you will see the data.  The point is that with this metadata you can query these tables to find the information you require on where data resides.

[CarthiQ]

It will not search each and every library. Sashelp.vcolumn contains details about the sas session (i.e. library names and dataset names). So by specifying the dataset name you can easily find its corresponding library.