topic Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide in SAS Programming

Querying Dictionary.Tables and Error Messages in Enterprise Guide

vfarmak — Wed, 08 Jun 2022 15:32:53 GMT

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

Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide

Tom — Wed, 08 Jun 2022 15:42:55 GMT

Why would a WORK dataset not exist?

Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide

vfarmak — Wed, 08 Jun 2022 15:46:41 GMT

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

Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide

mkeintz — Wed, 08 Jun 2022 21:23:32 GMT

@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?

Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide

Kurt_Bremser — Thu, 09 Jun 2022 05:46:17 GMT

WORK.POLICY_MEDI_AS400_INV

a dataset or a view?

Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide

vfarmak — Thu, 09 Jun 2022 09:01:14 GMT

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.

Re: Querying Dictionary.Tables and Error Messages in Enterprise Guide

Kurt_Bremser — Thu, 09 Jun 2022 09:11:03 GMT

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.