@Tom wrote:
Not sure how much the results from using SAS/Access to Oracle to access an Oracle database translate into using SAS/Access to ODBC to connect to a Teradata database.
It might not - but @Reeza statement was for "DB tables" in general and for this the Oracle engine appeared good enough to me.
Also: If the information doesn't make it into the dictionary tables then how comes we can normally see such tables in UI's like SAS EG under the library node. So I'm rather guessing that if DB tables aren't available in the SAS dictionary then there are some DB permissions "missing" site specific which prohibit the SAS user to query the DB metadata.
What I had to do to check if a Teradata table or view existed was query the Teradata metadata directly instead.
Here is part of a macro I made:
*----------------------------------------------------------------------------;
* Check if table or view exists ;
*----------------------------------------------------------------------------;
select obj into :result trimmed from connection to &connection
(select case when (tablekind in ('T','O')) then 'TABLE'
else 'VIEW' end as obj
from dbc.tablesv
where databasename = %squote(&db) and tablename= %squote(&table)
and tablekind in ('V','T','O')
)
;
%let exists=&sqlobs;
I also included code to check if there is any Volatile table with that name.
%if (^&exists and &db=&userid) %then %do;
*----------------------------------------------------------------------------;
* Check for any Volatile tables ;
*----------------------------------------------------------------------------;
select 1 into :exists from connection to &connection (help volatile table) ;
%if (&exists) %then %do;
*----------------------------------------------------------------------------;
* Check if this Volatile table exists ;
*----------------------------------------------------------------------------;
select 'VOLATILE TABLE' into :result
from connection to &connection (help volatile table)
%*----------------------------------------------------------------------------
Set VARNAME based on VALIDVARNAME setting.
-----------------------------------------------------------------------------;
%if %sysfunc(getoption(validvarname))=ANY %then
where upcase('table name'n) = "&table"
;%else
where upcase(table_name) = "&table"
;
;
%let exists=&sqlobs;
%end;
%end;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.