Hi
can anyone please answer me how to know the statistics collected or not on a tera data table (column level)which is used as an input in a SAS Di job.
i need to know it bcoz I am asked to prepare a user written transformation (which includes a macro calling with a %include statement)which will collect the stats from input tera data tables i am really struck with the logic
Hi sir,
it is statistics sir..
If the teradata is accessible in a library you should be able to get column information about a table with:
Proc sql;
select *
from dictionary.columns
where Libname= 'MYLIB' and memname='MYTABLE'
;
quit;
Where MYLIB is the name of your library in uppercase and MYTABLE is the name of the table also in upper case.
There are a number of dictionary tables to query depending on the information desired but the most of the meta data related to SAS is there, libraries, tables, macro varibles, titles, footnotes, file references and such.
Yes sir,it is the first one...I tried to find out with SAS metadata...but it is not there...so I need to query tera data and find out what statistics it has collected on a table it seems...I tried with sselect * from dbc. statstbl;statement but I am getting user does not access to the table...is my approach correct or is ther any other way?
If you cannot access the metadata views then perhaps you can just ask SAS to run the HELP STATISTICS command and capture the result into a table?
proc sql ;
connect to teradata ..... ;
create table stats as
select * from connection to teradata
(help statistics mydb.mytable)
;
quit;
Thanks sir,i hope it will work..but before that i struck with another thing..
i want to read each table schema at run time and call the table
using _input1 and _input2...macro variables i am able to extract the table names...
but i am unable to extract the schema of the tables..
as _input1_connect and _input2_connect ....macro variables are assigned as( %let _INPUT2_connect = SERVER="voda8.vodafone.com.au" AUTHDOMAIN="Teradata Auth")in the job.
is there any possibility to convert the libref to teradata schema ?
below is the _input macro variable..
%let _INPUT = TDMDLV.Subs_Anlyt_Item_Rslt;
Look at the SAS metadata table SASHELP.VLIBNAM.
The value of SYSVALUE where LIBNAME='your libref' and SYSNAME='Schema/User' should have the name of the database in Teradata.
Thank you so much for your help sir!!
Hello sir,hope you are doing well..thank you so much for your great help ..
now i am asked to do the same macro in oracle..
when i execute the query in sql developer it is working fine
below is the code:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'RTDMASL',
tabname => 'CMPGN_ACCT_1301',
cascade => false);
END;
but when i tried with sas enterprise guide i am getting an error..i tried with all the possibilities i can..
below is the code:
proc sql;
connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
execute
(
begin
dbms_stats.gather_table_stats(
ownname=> 'RTDMASL',
indname=> 'CMPGN_ACCT_1301' ,
cascade => false);
end;
) by oracle;
disconnect from oracle;
quit;
below is the error message i am getting:
ERROR: ORACLE execute error: ORA-06550: line 1, column 106:PLS-00103: Encountered the symbol ";" when expecting one of the
following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and
or like like2 like4 likec between || multiset member submultiset.
what is wrong with my code...
Hi, i am asked to create a job with userwritten ,job includes only transformation and no tables ,it should be able to collect stats on oracle tables in a particular schema in sas di.
here is the requirement:
Tables with CMPGN_* like will be included to collect the table stats in RTDMASL schema
> User written code module will be used to find out the cmpgn_* like tables
> User written code module will used to loop throgh the tables and collect the table stats
>
here is my code:
%macro CMPGN_TBL_Stats_Coll;
proc sql;
connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
create table cmpntbls as
select * from connection to oracle
(
SELECT
table_name, owner
FROM
all_tables
where owner='RTDMASL' and table_name like 'CMPGN_%'
ORDER BY
owner, table_name
);
disconnect from oracle
;
quit;
options symbolgen;
DATA _NULL_;
SET cmpntbls END=NOMORE;
CALL SYMPUT
('_TABLE' || COMPRESS(PUT(_N_,3.)),
TRIM(table_name));
call symput ('_schema',owner);
IF NOMORE then CALL SYMPUT
('TOT_TAB', PUT(_N_,3.));
RUN;
%DO I=1 %TO &TOT_TAB;
proc sql;
connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
execute(
begin
DBMS_STATS.GATHER_TABLE_STATS(
ownname=> &_schema,
tabname=> &&_table&i.,
cascade => false);
end;) by oracle;
disconnect from oracle;
quit;
%end;
%mend CMPGN_TBL_Stats_Coll;
%CMPGN_TBL_Stats_Coll;
and the error message i am getting is :
Line 215: ERROR: ORACLE execute error: ORA-06550: line 1, column 48: PLS-00201: identifier 'RTDMASL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.