cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14 Ronein
Meteorite | Level 14

Create TeraData VOLATILE TABLE from SAS

Posted 01-08-2024 01:18 AM (949 views)

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
0 Likes
Reply
5 REPLIES 5
LinusH
Tourmaline | Level 20 LinusH
Tourmaline | Level 20

Re: Create TeraData VOLATILE TABLE from SAS

Posted 01-08-2024 04:10 AM (911 views)  |   In reply to Ronein

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
Reply
Amir
PROC Star Amir
PROC Star

Re: Create TeraData VOLATILE TABLE from SAS

Posted 01-08-2024 04:14 AM (905 views)  |   In reply to Ronein

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.

0 Likes
Reply
Patrick
Opal | Level 21 Patrick
Opal | Level 21

Re: Create TeraData VOLATILE TABLE from SAS

Posted 01-08-2024 04:15 AM (904 views)  |   In reply to Ronein

Patrick_1-1704705089135.png

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

 

Patrick_2-1704705179254.png

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

Reply
Ronein
Meteorite | Level 14 Ronein
Meteorite | Level 14

Re: Create TeraData VOLATILE TABLE from SAS

Posted 01-08-2024 04:35 AM (889 views)  |   In reply to Patrick
Thanks. Can you fix my code and show the required code to create tera violate table ( code run in sas)?
0 Likes
Reply
LinusH
Tourmaline | Level 20 LinusH
Tourmaline | Level 20

Re: Create TeraData VOLATILE TABLE from SAS

Posted 01-08-2024 06:30 AM (866 views)  |   In reply to Ronein

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
0 Likes
Reply

SAS Innovate 2025: Register Today!

 

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • ‎01-08-2024 01:18 AM
  • 950 views
  • 3 likes
  • 4 in conversation