BookmarkSubscribeRSS Feed
gamotte
Rhodochrosite | Level 12

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 !

2 REPLIES 2
data_null__
Jade | Level 19

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
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1121 views
  • 0 likes
  • 2 in conversation