BookmarkSubscribeRSS Feed
Sai1
Fluorite | Level 6

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

12 REPLIES 12
Sai1
Fluorite | Level 6

Hi sir,

it is statistics sir..

ballardw
Super User

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.

Sai1
Fluorite | Level 6
Spoiler
Thank you sir,for your reply.

I am at intermediate level in SAS ...I tried with dictionary.columns and other dictionary tables too,but I am unable to find the information about the statistics collected on a table(column level)..please assist me further 

Tom
Super User Tom
Super User
The answer depends on what you mean by "statistics collected". COLLECT STATISTICS has a special meaning in Teradata. Are you asking how to query Teradata and find out what statistics it has collected on a table? Or are you just asking how to get information on a table such as the column names or number of records?
Sai1
Fluorite | Level 6

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?

Tom
Super User Tom
Super User

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;
Sai1
Fluorite | Level 6

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;

Tom
Super User Tom
Super User

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.

Sai1
Fluorite | Level 6

Thank you so much for your help sir!!

Sai1
Fluorite | Level 6

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...

 

 

Sai1
Fluorite | Level 6

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.

 

 

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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 12 replies
  • 3815 views
  • 3 likes
  • 4 in conversation