topic Re: macro to collect stats of tera data tables in SAS job in SAS Studio

macro to collect stats of tera data tables in SAS job

Sai1 — Mon, 31 Oct 2016 08:09:47 GMT

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

Re: macro to collect stats of tera data tables in SAS job

Kurt_Bremser — Mon, 31 Oct 2016 08:31:33 GMT

What do you mean by "stats"?

Re: macro to collect stats of tera data tables in SAS job

ballardw — Mon, 31 Oct 2016 17:05:42 GMT

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.

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Mon, 31 Oct 2016 19:18:00 GMT

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

Re: macro to collect stats of tera data tables in SAS job

Tom — Mon, 31 Oct 2016 19:24:52 GMT

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?

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Mon, 31 Oct 2016 19:47:34 GMT

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?

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Mon, 31 Oct 2016 19:56:29 GMT

Hi sir,

it is statistics sir..

Re: macro to collect stats of tera data tables in SAS job

Tom — Mon, 31 Oct 2016 20:57:45 GMT

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;

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Tue, 01 Nov 2016 01:25:58 GMT

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;

Re: macro to collect stats of tera data tables in SAS job

Tom — Tue, 01 Nov 2016 16:06:07 GMT

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.

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Tue, 01 Nov 2016 17:30:12 GMT

Thank you so much for your help sir!!

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Thu, 03 Nov 2016 19:59:09 GMT

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

Re: macro to collect stats of tera data tables in SAS job

Sai1 — Fri, 04 Nov 2016 04:07:11 GMT

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.