08-13-2015 10:04 AM
I have two below Questions, please can you help with them.
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
Is there any way to find that from Server or from SAS
Management console or any other way.
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
08-13-2015 10:21 AM
Well, I am not familiar with EG, however in Base SAS there is the metadata tables which you can query. In code:
create table WANT as
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.
08-13-2015 11:01 AM
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.
table test085 as select * from DICTIONARY.TABLES
08-13-2015 11:54 AM
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.
PROC CONTENTS data =&mylib._ALL_ out=&mycontents NOPRINT /* optional: */ (KEEP = LIBNAME MEMNAME) ;
proc sort data = &mycontents nodupkey;
by libname memname;
/*Eample for executing the macro*/
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.
08-13-2015 01:38 PM
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.
08-14-2015 05:05 AM
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.
08-14-2015 10:40 AM
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.
Actually, this would make a great Coder's Corner paper.
08-14-2015 11:10 AM
Just to muddy the waters a bit, remember it is possible that more than one program replaces the same data set.
08-14-2015 11:38 AM
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.
08-14-2015 06:32 AM
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.
08-14-2015 11:39 AM
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:
08-20-2015 12:20 PM
For your second question below code will return the library name of specific dataset.
select distinct libname from sashelp.vcolumn where upcase(memname) = "DATASETS NAME";
08-21-2015 12:01 AM
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
08-21-2015 03:54 AM
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.
08-21-2015 01:40 PM
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.