Create TeraData VOLATILE TABLE from SAS

Ronein · Posted 01-08-2024 01:18 AM

Hello

I run the following query in SAS.

This query should connect to TERADATA and create Volatile table (Empty table with no rows).

proc sql;
connect to teradata(server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL);
execute
(CREATE multiset VOLATILE TABLE temp1(NAME VARCHAR(10), STATE CHAR(3))
NO PRIMARY INDEX ON COMMIT PRESERVE ROWS)	by teradata;
execute ( COMMIT WORK ) by teradata;
quit;

My questions:

1-How can I know If the query worked well and the desired violated table was created in Tera?

Here is the Log.

IF it was not created then can you help fix the code?

2- I opened TeraData and run

Select * from temp1

But got error "Object temp1" doesn't exist.

1                                                          The SAS System                              08:12 Monday, January 8, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         
27         %include '!RSMEHOME/SASCode/SHARECode/Libname.sas';
NOTE: Libref RET_EXT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/ABT_Retail_SME/ETL_DataMart/Extract
NOTE: Libref RET_DM refers to the same physical library as RET_DM.
NOTE: Libref RET_DM was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/ABT_Retail_SME/ETL_DataMart/DataMart
NOTE: Libref ABT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/ABT_Retail_SME/ETL_ABT/ABT
NOTE: Libref INOUTCOM was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/income_outcome
NOTE: Libref MEMUZAG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/memuzag
NOTE: Libref CATALOG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/catalog
NOTE: Libref EXTRA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/extra
NOTE: Libref NIKUD refers to the same physical library as A38.
NOTE: Libref NIKUD was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/A38
NOTE: Libref BAKASHOT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/bakashot
NOTE: Libref HALV was successfully assigned as follows: 
2                                                          The SAS System                              08:12 Monday, January 8, 2024

      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/halv
NOTE: Libref ITHALV was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/ithalv
NOTE: Libref HOVAV was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/hovav
NOTE: Libref BTCHONOT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/btchonot
NOTE: Libref HAFRASHA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/hafrasha
NOTE: Libref SHELANU was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/shelanu
NOTE: Libref NUMAIN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/numaIn
NOTE: Libref NUMAOUT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/numaOut
NOTE: Libref NWSCRCB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld_CB/score_cb/Nwscrcb
NOTE: Libref CB_TAN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld_CB/score_cb/cb_tan
NOTE: Libref PAN_TAN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/pan_tan
NOTE: Libref EXT_TAN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/ext_tan
NOTE: Libref SIKUN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/dohot_cs/sikun/output
NOTE: Libref DOHOT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/dohot
NOTE: Libref LGDBLL was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/LGDBll
NOTE: Libref LGDCB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/LGDCB
NOTE: Libref LGD was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs
NOTE: Libref DRGSYS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/drgsys
NOTE: Libref RISKCONT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/riskcont
NOTE: Libref DATACS was successfully assigned as follows: 
      Engine:        V9 
3                                                          The SAS System                              08:12 Monday, January 8, 2024

      Physical Name: /usr/local/SAS/MidulOld/score_cs/dataCS
NOTE: Libref OUTCS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/outCS
NOTE: Libref A38 refers to the same physical library as NIKUD.
NOTE: Libref A38 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/A38
NOTE: Libref CATALOG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/catalog
NOTE: Libref MEMUZAG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/memuzag
NOTE: Libref BTCHONOT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/btchonot
NOTE: Libref PAN_TAN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/pan_tan
NOTE: Libref TXT_CSV was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/txt_csv
NOTE: Libref DOHOT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/dohot
NOTE: Libref BDIKOT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/dohot/bdikot
NOTE: Libref LGDBLL was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/LGDbll
NOTE: Libref EADCS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/Eadcs
NOTE: Libref LOG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOldARV/Log
NOTE: Libref T_AVODA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/t_avoda
NOTE: Libref MODELCB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld_CB/score_cb/General/Model_CB
NOTE: Libref SASTAB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/PARETO/BAKASHOT_SHONOT_SIGAL/SASTAB
NOTE: Libref NWSCRCB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld_CB/score_cb/Nwscrcb
NOTE: Libref CATALOG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/catalog
NOTE: Libref ARCBDATA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOldARV_CB/score_cb/arcbdata
NOTE: Libref RET_EXT was successfully assigned as follows: 
      Engine:        V9 
4                                                          The SAS System                              08:12 Monday, January 8, 2024

      Physical Name: /usr/local/SAS/ABT_Retail_SME/ETL_DataMart/Extract
NOTE: Libref ORLY was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/Orly
NOTE: Libref APP was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/SASData/app
NOTE: Library SAM does not exist.
NOTE: Libref CB_1AD5 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/cb_1ad5
NOTE: Libref TEST was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/test
NOTE: Libref EAD_CS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/EAD_CS
NOTE: Library SIM_IKL does not exist.
NOTE: Library CRA does not exist.
NOTE: Library USERDIRC does not exist.
NOTE: Libref AYELET was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/ayelet
NOTE: Libref KARINA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/karina
NOTE: Libref NATALYA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/Natalya
NOTE: Libref KFIR was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/kfir
NOTE: Libref BCG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/bcg
NOTE: Libref R_R was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/R_R
NOTE: Libref TSUA_HON was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/TSUA_HON
NOTE: Libref CSPITUAH was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/SASData/cspituah
NOTE: Library DATA does not exist.
NOTE: Library ABTCS does not exist.
NOTE: Libref MALMASH was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/kfir/Malmash
NOTE: Libref BALMASH was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/Balmash
NOTE: Library BACKTEST does not exist.
NOTE: Library BTABLES does not exist.
NOTE: Libref CS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users
NOTE: Library DIR_CB does not exist.
5                                                          The SAS System                              08:12 Monday, January 8, 2024

NOTE: Library DIR_CS does not exist.
NOTE: Libref HADATZ3 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld_CB/score_cb/MF/MF2NT
NOTE: Libref ABT_CS was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/ABT_CS
NOTE: Library ECLIB000 does not exist.
NOTE: Libref ARVIDKUN was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOldARV/score_cs/arvidkun
NOTE: Library SASTAB does not exist.
NOTE: Libref SASTAB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/PARETO/BAKASHOT_SHONOT_SIGAL/SASTAB
NOTE: Libref BDI_CB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users/BDI_CB
NOTE: Libref M_HOVAV was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/SASData/M_HOVAV
NOTE: Libref MAARAHIM was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/SASData/MAARAHIM
NOTE: Libref NWSCRCB was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld_CB/score_cb/Nwscrcb
NOTE: Libref CATALOG was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOld/score_cs/catalog
NOTE: Libref ARCBDATA was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/MidulOldARV_CB/score_cb/arcbdata
NOTE: Libref RET_EXT was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/ABT_Retail_SME/ETL_DataMart/Extract
NOTE: Libref SAPIM was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/SASData/sapim
NOTE: Libref PANELTZ was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/SASCSB/MidulOld/score_cs/panel_tz
NOTE: Libref CB_1AD5 was successfully assigned as follows: 
      Engine:        V9 
      Physical Name: /usr/local/SAS/SASUsers/LabRet/SASData/CB_1AD5
152        
153        proc sql;
154        connect to teradata(server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL);
155        execute
156        (CREATE multiset VOLATILE TABLE temp1(NAME VARCHAR(10), STATE CHAR(3))
157        NO PRIMARY INDEX ON COMMIT PRESERVE ROWS)	by teradata;
158        execute ( COMMIT WORK ) by teradata;
159        quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.60 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              100.03k
6                                                          The SAS System                              08:12 Monday, January 8, 2024

      OS Memory           20640.00k
      Timestamp           01/08/2024 08:15:12 AM
      Step Count                        3  Switch Count  18
      Page Faults                       0
      Page Reclaims                     29
      Page Swaps                        0
      Voluntary Context Switches        125
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

160        
161        GOPTIONS NOACCESSIBLE;
162        %LET _CLIENTTASKLABEL=;
163        %LET _CLIENTPROCESSFLOWNAME=;
164        %LET _CLIENTPROJECTPATH=;
165        %LET _CLIENTPROJECTPATHHOST=;
166        %LET _CLIENTPROJECTNAME=;
167        %LET _SASPROGRAMFILE=;
168        %LET _SASPROGRAMFILEHOST=;
169        
170        ;*';*";*/;quit;run;
171        ODS _ALL_ CLOSE;
172        
173        
174        QUIT; RUN;
175

LinusH · Posted 01-08-2024 04:10 AM

For some instances, the RDBMS can send back a reurn code/message. Check macro variables SQLXRC and SQLXMSG after the EXECUTE block.

Another option to get more detailed feedback from Teradata is to use a libref instead, with DBMSTEMP=YES.

Then you can get detailed feedback in te log by using:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

The downside is that you might be a little limited in how to specify yor table definition.

Data never sleeps

Amir · Posted 01-08-2024 04:14 AM

Hi,

Have you tried examining the automatic macro variable sqlrc? That and other macro variables you can check can be seen in more detail in the documentation: Using the PROC SQL Automatic Macro Variables

Thanks & kind regards,

Amir.

Patrick · Posted 01-08-2024 04:15 AM

How? Needs to be in the SAME SAS session with a global connection as else the VOLATILE table won't exist.

Creating a table should never require a commit. Changing rows (data) in a table is what needs a commit.

Ronein · Posted 01-08-2024 04:35 AM

Thanks. Can you fix my code and show the required code to create tera violate table ( code run in sas)?

LinusH · Posted 01-08-2024 06:30 AM

Something like this (untested due to lack of a Teradata connection):

libanme TD_tmp teradata db=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL dbmstemp=yes;

proc sql;
   CREATE TABLE TD_temp.temp1 (NAME VARCHAR(10), STATE CHAR(3));
quit;

Data never sleeps

Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Re: Create TeraData VOLATILE TABLE from SAS

Ready to join fellow brilliant minds for the SAS Hackathon?

Click image to register for webinar

Classroom Training Available!