dictionary.column

gamotte — Thu, 27 Sep 2012 16:03:25 GMT

Hi,

I have a SAS table, say A, with two columns with library names and sas datasets names

corresponding to some selected datasets on my SAS installation.

LIB TABLE

lib1 tab1

lib1 tab2

lib2 tab3

so, for instance, lib1.tab2 is a valid table name on my installation.

I wish to retrieve metadata for those tables using a proc sql similar to the one below :

proc sql;

SELECT A.LIB, A.TABLE, B.NAME, B.TYPE, B.LENGTH

FROM A

LEFT JOIN dictionary.columns B

ON B.LIBNAME=strip(upcase(A.LIB)) and B.MEMNAME=strip(upcase(A.TABLE));

quit;

It works but it seems quite slow. Also, the log contains many messages such as

NOTE: Data file XXX.DATA is in a format native to another

host or the file encoding does not match the session encoding. Cross Environment Data

with XXX being data files names.

I understand the message since I work with windows SAS and some

data files have been created under AIX.

What I do not understand is that it is displayed for data files that

are not referred to in the table A.

It seems that this query systematically parses every dataset from every libname

before taking into account the condition in the join.

Is ther a way to optimize this query in order to filter the data sets

before reading the associated data ?

Thanks !

Re: dictionary.column

data_null__ — Thu, 27 Sep 2012 16:08:47 GMT

Use PROC CONTENTS or the CONTENTS statement of DATASETS.

As you can see dictionary.columns wants too gather meta data from "everywhere" and then filter it.

PROC CONTENTS will be much faster.

gamotte — Thu, 27 Sep 2012 20:16:36 GMT

Thank you, I will do that.

It is strange though that when I use the following query with

a table that has been created under AIX

SELECT NAME, TYPE, LENGTH

FROM dictionary.columns

WHERE LIBNAME="somelib" and MEMNAME="somedata";

I only get the previous message for the selected table.

It is like SAS prefilters the data only if a unique dataset is selected.