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