NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.2 (TS2M3)
Licensed to Deschamps Geraldine-E2703, Site 70068112.
NOTE: This session is executing on the XP_PRO platform.
NOTE: SAS initialization used:
real time 3.09 seconds
cpu time 0.79 seconds
1 options sastrace=",,,db" sastraceloc=saslog nostsuffix ;
2 libname demo teradata server="vmtd" user="adamm" password=XXXXXX schema="demo_base_dwh" ;
NOTE: Libref DEMO was successfully assigned as follows:
Engine: TERADATA
Physical Name: vmtd
3
4 /* EXEMPLE 1- Sans In-Database*/
TERADATA_0: Prepared: on connection 1
SELECT * FROM demo_base_dwh."brs"
5 data Resultat ;
6 set demo.brs (where=(tv_type_cd='PLASMA'));
7 length PaysFabricant $20. ;
8 Select (TV_BRAND_CD);
9 when ('LG') PaysFabricant='Cor�e' ;
10 when ('MITSUBISHI') PaysFabricant='Japon' ;
11 when ('PANASONIC') PaysFabricant='Japon' ;
12 when ('SAMSUNG') PaysFabricant='Cor�e' ;
13 when ('SONY') PaysFabricant='Japon' ;
14 when ('VISIO') PaysFabricant='Etats-Unis' ;
15 otherwise ;
16 end ;
17 run ;
TERADATA: trqacol- No casting. Raw row size=42, Casted size=42, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_1: Prepared: on connection 1
SELECT "TV_TYPE_CD","TV_ID","TV_MODEL_NM","TV_MODEL_COST","TV_BRAND_CD","TV_MSRP_AMT" FROM demo_base_dwh."brs" WHERE
("TV_TYPE_CD" = 'PLASMA' )
TERADATA_2: Executed: on connection 1
SELECT "TV_TYPE_CD","TV_ID","TV_MODEL_NM","TV_MODEL_COST","TV_BRAND_CD","TV_MSRP_AMT" FROM demo_base_dwh."brs" WHERE
("TV_TYPE_CD" = 'PLASMA' )
TERADATA: trget - rows to fetch: 15
NOTE: There were 15 observations read from the data set DEMO.brs.
WHERE tv_type_cd='PLASMA';
NOTE: The data set WORK.RESULTAT has 15 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.57 seconds
cpu time 0.09 seconds
18
19 /* EXEMPLE 2- Proc SORT : avec SortPgm le tri peut �tre r�alis� par la DB*/
20 option sortpgm=best;
21
22 /* -> ex�cut�e � 100% par Teradata */
TERADATA_3: Prepared: on connection 1
SELECT * FROM demo_base_dwh."brs"
23 proc sort data=demo.brs out=brs ;
24 by tv_id ;
25
26 run ;
TERADATA_4: Prepared: on connection 2
SELECT * FROM demo_base_dwh."brs"
TERADATA: trqacol- No casting. Raw row size=42, Casted size=42, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_5: Prepared: on connection 1
SELECT "TV_ID","TV_MODEL_NM","TV_MODEL_COST","TV_BRAND_CD","TV_TYPE_CD","TV_MSRP_AMT" FROM demo_base_dwh."brs" ORDER BY "TV_ID"
TERADATA_6: Executed: on connection 1
SELECT "TV_ID","TV_MODEL_NM","TV_MODEL_COST","TV_BRAND_CD","TV_TYPE_CD","TV_MSRP_AMT" FROM demo_base_dwh."brs" ORDER BY "TV_ID"
TERADATA: trget - rows to fetch: 73
NOTE: Sorting was performed by the data source.
NOTE: There were 73 observations read from the data set DEMO.brs.
NOTE: The data set WORK.BRS has 73 observations and 6 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.31 seconds
cpu time 0.04 seconds
27
28 /* -> non ex�cut�e par Teradata � cause de l'option noduplicate */
29
TERADATA_7: Prepared: on connection 1
SELECT * FROM demo_base_dwh."brs"
30 proc sort data=demo.brs out=brs noduplicate;
31 by tv_id ;
32 run ;
TERADATA_8: Prepared: on connection 2
SELECT * FROM demo_base_dwh."brs"
TERADATA: trqacol- No casting. Raw row size=42, Casted size=42, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_9: Prepared: on connection 1
SELECT "TV_ID","TV_MODEL_NM","TV_MODEL_COST","TV_BRAND_CD","TV_TYPE_CD","TV_MSRP_AMT" FROM demo_base_dwh."brs"
TERADATA_10: Executed: on connection 1
SELECT "TV_ID","TV_MODEL_NM","TV_MODEL_COST","TV_BRAND_CD","TV_TYPE_CD","TV_MSRP_AMT" FROM demo_base_dwh."brs"
TERADATA: trget - rows to fetch: 73
TERADATA_11: Prepared: on connection 2
SELECT * FROM demo_base_dwh."brs"
NOTE: There were 73 observations read from the data set DEMO.brs.
NOTE: 0 duplicate observations were deleted.
NOTE: The data set WORK.BRS has 73 observations and 6 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.15 seconds
cpu time 0.03 seconds
33
34
35 /* EXEMPLE 3- SAS/Access IDB*/
36 option sqlgeneration=ALL ;
TERADATA_12: Prepared: on connection 1
SELECT * FROM demo_base_dwh."brs"
37 proc means data=demo.brs sum mean ;
38 var tv_model_cost ;
39 class tv_type_cd;
40 run ;
TERADATA: tryoeinf()
TERADATA: tryoeinf()
TERADATA: tryoeinf()
NOTE: SQL generation will be used to perform the initial summarization.
TERADATA_13: Prepared: on connection 3
SELECT * FROM demo_base_dwh."brs"
TERADATA_14: Prepared: on connection 4
select COUNT(*) as "ZSQL1", MIN(TXT_1."TV_TYPE_CD") as "ZSQL2", COUNT(*) as "ZSQL3", COUNT(TXT_1."TV_MODEL_COST") as "ZSQL4",
SUM(TXT_1."TV_MODEL_COST") as "ZSQL5" from "demo_base_dwh"."brs" TXT_1 group by TXT_1."TV_TYPE_CD"
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
TERADATA_15: Executed: on connection 4
select COUNT(*) as "ZSQL1", MIN(TXT_1."TV_TYPE_CD") as "ZSQL2", COUNT(*) as "ZSQL3", COUNT(TXT_1."TV_MODEL_COST") as "ZSQL4",
SUM(TXT_1."TV_MODEL_COST") as "ZSQL5" from "demo_base_dwh"."brs" TXT_1 group by TXT_1."TV_TYPE_CD"
TERADATA: trget - rows to fetch: 5
NOTE: PROCEDURE MEANS used (Total process time):
real time 0.70 seconds
cpu time 0.21 seconds
41
TERADATA_16: Prepared: on connection 1
SELECT * FROM demo_base_dwh."brs"
42 proc freq data=demo.brs ;
43 table tv_type_cd;
44 run ;
TERADATA: tryoeinf()
TERADATA: tryoeinf()
TERADATA: tryoeinf()
TERADATA_17: Prepared: on connection 3
SELECT * FROM demo_base_dwh."brs"
TERADATA_18: Prepared: on connection 4
select COUNT(*) as "ZSQL1", case when COUNT(*) > COUNT(TXT_1."TV_TYPE_CD") then ' ' else MIN(TXT_1."TV_TYPE_CD") end as "ZSQL2"
from "demo_base_dwh"."brs" TXT_1 group by TXT_1."TV_TYPE_CD"
NOTE: SQL generation will be used to construct frequency and crosstabulation tables.
ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.
TERADATA_19: Executed: on connection 4
select COUNT(*) as "ZSQL1", case when COUNT(*) > COUNT(TXT_1."TV_TYPE_CD") then ' ' else MIN(TXT_1."TV_TYPE_CD") end as "ZSQL2"
from "demo_base_dwh"."brs" TXT_1 group by TXT_1."TV_TYPE_CD"
TERADATA: trget - rows to fetch: 5
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.59 seconds
cpu time 0.14 seconds
45
46
47 /* EXEMPLE 4 - SAS/Access : SQL implicite - le WHERE est soumis par le SGBD */
TERADATA_20: Prepared: on connection 1
SELECT * FROM demo_base_dwh."brs"
48 proc transpose data=demo.brs out=Resultat (drop=_NAME_ _LABEL_) delimiter=_ suffix=_cost;
49 var TV_MODEL_COST ;
50 id TV_BRAND_CD TV_MODEL_NM ;
51 by TV_TYPE_CD;
52 where SUBSTR (TV_MODEL_NM,1,1)='4' ;
53
54 run ;
TERADATA: trqacol- No casting. Raw row size=26, Casted size=26, CAST_OVERHEAD_MAXPERCENT=20%
TERADATA_21: Prepared: on connection 1
SELECT "TV_MODEL_COST","TV_BRAND_CD","TV_MODEL_NM","TV_TYPE_CD" FROM demo_base_dwh."brs" WHERE ( SUBSTR("TV_MODEL_NM", 1, 1) =
'4' ) ORDER BY "TV_TYPE_CD"
TERADATA_22: Executed: on connection 1
SELECT "TV_MODEL_COST","TV_BRAND_CD","TV_MODEL_NM","TV_TYPE_CD" FROM demo_base_dwh."brs" WHERE ( SUBSTR("TV_MODEL_NM", 1, 1) =
'4' ) ORDER BY "TV_TYPE_CD"
TERADATA: trget - rows to fetch: 21
NOTE: The above message was for the following BY group:
TV_TYPE_CD=DLP
NOTE: The data set WORK.RESULTAT has 4 observations and 16 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.32 seconds
cpu time 0.03 seconds
55
56
57
58 /* EXEMPLE 5- SAS/Access : SQL explicite */
59 proc sql ;
60 connect to teradata (server="vmtd" user=adamm password=XXXXXX schema="demo_base_dwh" ) ;
61 create table Resultats as
62 select * from connection to teradata
63 (select TableName, ColumnName, ColumnType from DBC.Columns where DatabaseName='Demo_Base_DWH')
64 ;
TERADATA_23: Prepared: on connection 1
select TableName, ColumnName, ColumnType from DBC.Columns where DatabaseName='Demo_Base_DWH'
TERADATA_24: Executed: on connection 1
select TableName, ColumnName, ColumnType from DBC.Columns where DatabaseName='Demo_Base_DWH'
TERADATA: trget - rows to fetch: 78
NOTE: Table WORK.RESULTATS created, with 78 rows and 3 columns.
65 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.31 seconds
cpu time 0.01 seconds