Posted on 2012-07-20 16:12:50 by chris
I recently came across a situation at work where I was getting some performance issues with a program because of the way it had been written to find some libraries that it needed. What was happening was that if you have a number of Teradata libraries assigned in your SAS session and you use sashelp.vslib to query what libraries you have assigned you will see that SAS goes off an queries the Teradata dbc.tables table for information about the tables SAS knows about. If there are lots and/or if these tables are large, then you could see a significant drop in the performance of your job while this exercise takes place.
To get around this problem, we need to use a slightly different method of checking which libraries we have assigned in the session: dictionary.libnames. We use this in conjunction with proc SQL:
%let lib1 = work ;
proc sql ;
create table &lib1..teralibs as
select libname
from dictionary.libnames
where engine="TERADATA" ;
quit ;
proc sql;
select count(*)
into :NObs
from &lib1..teralibs;
select libname
into :libname1-:libname%left(&NObs)
from &lib1..teralibs;
%put &libname1;
quit;
%macro AssignLib ;
%do i=1 %to &NObs;
libname &&libname&i '';
%end;
%mend;
%AssignLib ;
A break down of the code:
Firstly we extract the Teradata librefs from the dictionary table of assigned libraries.
proc sql ;
create table &lib1..teralibs as
select libname
from dictionary.libnames
where engine="TERADATA" ;
quit ;
Assign the librefs that have been extracted to a variable for more processing later on.
proc sql;
select count(*)
into :NObs
from &lib1..teralibs;
select libname
into :libname1-:libname%left(&NObs)
from &lib1..teralibs;
%put &libname1;
quit;
For each libref that we have extracted run a libname statement temporarily assigning the libref to a filesystem location. The reason that the libraries are reassigned as opposed to cleared or unassigned is because when a “libname x clear ;” statement is run an out-of-scope ERROR message was received.
%macro AssignLib ;
%do i=1 %to &NObs;
libname &&libname&i '';
%end;
%mend;
%AssignLib ;
%let lib1 = work ;