topic wird behavior when using exists with libname assigned viea metadata in SAS Programming

wird behavior when using exists with libname assigned viea metadata

rudfaden — Tue, 03 Oct 2023 08:37:45 GMT

When using the "metaout" option in SAS on a SQL database views, behavior varies based on the option:

With "metaout=ALL," use "exist(view)" to get true. Example:

data _null_;
rc = libname("jb_dw", "", "META", "liburi=""SASLibrary?@libref='jb_dw'"" metaout=ALL");
if rc ne 0 then do;
   put rc=;
   put ms=;
end;
run;

%put exist=%sysfunc(exist(jb_dw.BANKDAGE_STAMOPL)); *false;
%put exist_view=%sysfunc(exist(jb_dw.BANKDAGE_STAMOPL,view)); *true;

With "metaout=data," use "exist" (without "view") to get true. Example:

data _null_;
rc = libname("jb_dw", "", "META", "liburi=""SASLibrary?@libref='jb_dw'"" metaout=data");
if rc ne 0 then do;
   put rc=;
   put ms=;
end;
run;
%put exist=%sysfunc(exist(jb_dw.BANKDAGE_STAMOPL)); *true;
%put exist_view=%sysfunc(exist(jb_dw.BANKDAGE_STAMOPL,view)); *false;

Can anybody explain this behavior?

Re: wird behavior when using exists with libname assigned viea metadata

s_lassen — Tue, 03 Oct 2023 14:51:52 GMT

I think the point is that when you use the METAOUT=DATA, the libname refers to the physical library. The documentation says

specifies that you can read, create, update, and delete physical tables.

Interaction: The user can access any table, regardless of whether it has been defined in the repository.

As the library is on SQL server, and SAS allows access to all tables or views, regardless of whether they have been defined in metadata or not, SAS does not know if it is a table or a view, because it reads the SQL server directory, not the metadata.

When you use METAOUT=ALL, the documentation specifies that you can only access tables or views that are registered in metadata. Which means that SAS uses the metadata repository to find the tables or views, and if your view is correctly registered there, it will show as a view.

Re: wird behavior when using exists with libname assigned viea metadata

rudfaden — Wed, 04 Oct 2023 10:12:53 GMT

Don't see how this explains the behavior. Why would this lead to a difference en how exists work?

FIY, the table is registred in metadata.

Re: wird behavior when using exists with libname assigned viea metadata

s_lassen — Wed, 04 Oct 2023 19:10:42 GMT

Because one option (METAOUT=ALL) asks SAS to check the metadata repository for information about tables and views, while the other(METAOUT=DATA) lets SAS access the SQL Server directory directly. When you use the direct access, SAS does NOT read the metadata, but just reads the SQL server directory - this basic service will show you what is physically there, but will give you the same result as assigning the library with a basic SQL Server libname statement.

So one option gives you access to the metadata (but will not show you tables or views not registered in metadata), the other will show what's actually on the SQL server when you ask, but that option does not read the metadata to see if you are dealing with a table or a view. On the other hand, it will show you what's actually there (also tables or views that are not registered in metadata, but not tables or views that are registered in metadata, but which do not actually exist). The latter option does not read the metadata, and does not know if something is a table or a view.