Querying Dictionary.Tables and Error Messages in Enterprise Guide

[vfarmak]

Dear SAS Communities,

I am facing the below problem: when I try to query the Dictionary.Tables, error messages appear that denotes mistakes made either in ETL flows or in tables in general.

Sometimes, if the table is located in a registered library, I can perform from SAS Data Integration Studio the Update metadata action and the problem is fixed.

What should I in case the errors relate to the WORK library?

I will give you an example of the error log:

30         PROC SQL;
31         CREATE TABLE TABLE_INFO AS
32         SELECT 	CATX(".", t1.LIBNAME, t1.MEMNAME) AS TABLE_NAME
33         	, t1.libname
34         	, t1.memname
35         	, t1.nobs as number_of_lines
36         	, "POLICY_MEDI_AS400_INV" as Target_Table
37         FROM 	DICTIONARY.TABLES t1 INNER JOIN
38         		WORK.POLICY_MEDI_AS400_INV t2 ON t1.MEMNAME = t2.Table_Name;
ERROR: File WORK.EXPENSES_APPEND.DATA does not exist.
ERROR: File WORK.W63JIK64.DATA does not exist.
ERROR: File WORK.W6ENSJCK.DATA does not exist.
ERROR: File WORK.W6ENRHRZ.DATA does not exist.

Best Regards,

Vassileios

[Tom]

Why would a WORK dataset not exist?

[vfarmak]

In the work library are stored usually tables that are stored temporarily in a SAS session.

The problem is that I cannot find the tables in the WORK library (because they do not exist) and I need to find a way to search these tables in the metadata server (if they exist, so as to delete them).

[mkeintz]

---

@vfarmak wrote:

In the work library are stored usually tables that are stored temporarily in a SAS session.

The problem is that I cannot find the tables in the WORK library (because they do not exist) and I need to find a way to search these tables in the metadata server (if they exist, so as to delete them).

---

But the real question is why does the metadata server (i.e. the dictionary feature of PROC SQL) think these dataset files exist, when in fact they don't?   Does this happen regularly?

Question: is there a possibility that the directory assigned to the work library also in use by another SAS process?

[Kurt_Bremser]

Is 

WORK.POLICY_MEDI_AS400_INV

a dataset or a view?

[vfarmak]

It is a dataset and not a view.
I tried to Query the Dictionary.Tables to find out about these types of messages (that include the error).
I searched and found out the PROC METALIB, but I do not know if I can list the problematic tables.

[Kurt_Bremser · Accepted solution]

Use of a DICTIONARY table in SQL causes a live scan of the requested objects. In the case of DICTIONARY.TABLES, SAS SQL does a directory listing of all assigned libraries, searching for *.sas7bdat, and then scans all found files for their metadata. While doing that, it converts all names to lowercase, so if you have a file

Expenses_append.sas7bdat

in your WORK, the file is found, but then SAS tries to read from

expenses_append.sas7bdat

and issues the message

ERROR: File WORK.EXPENSES_APPEND.DATA does not exist.

Is there anything in your code where you create stuff in WORK using file copy mechanisms or similar, bypassing the usual methods for creating datasets?

If this is not the case, your issue looks very much like something that requires opening a track with SAS Technical Support.