Re: sas to tera

Ronein

Hello

Lets say that I am working in sas and select data from teradata table view.

I know 2 ways that are working 100% well

proc sql;  
connect to teradata (server=dwprod schema=DWP_vall authdomain=TeraDataAuth);
create table WAY1  as 
select * from connection to teradata
(
select top 5 *
from VBM374_USED_BRANCH_CUSTOMER
);
disconnect from teradata;
quit ;


proc sql outobs=5;
create table WAY2 as
select *
from teradata.VBM374_USED_BRANCH_CUSTOMER
;
quit;

The connection between sas and tera was done by sas admin.

As I understand Way1 is connection to teradata and here MUST use Teradata SQL functions (such as QUALIFY,RANK,OVERmpartition,row_number)

As I understand Way2 is via libname statement and here MUST use SAS language

(Access Teradata Table Using LIBNAME Statement)

My question-How can I know what is the libaname code that connect between sas and tera here???

I dont need to run the libaname in order to run it but I think that in background it is running but I dont know what libname is running .

At beginning I run include

%include '!RSMEHOME/SASCode/SHARECode/Libname.sas';

Here is the log or running the include

1                                                          The SAS System                         09:02 Saturday, September 20, 2025

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release.
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         %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 
2                                                          The SAS System                         09:02 Saturday, September 20, 2025

      Physical Name: /usr/local/SAS/MidulOld/score_cs/bakashot
NOTE: Libref HALV was successfully assigned as follows: 
      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
3                                                          The SAS System                         09:02 Saturday, September 20, 2025

NOTE: Libref DATACS was successfully assigned as follows: 
      Engine:        V9 
      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
4                                                          The SAS System                         09:02 Saturday, September 20, 2025

NOTE: Libref RET_EXT was successfully assigned as follows: 
      Engine:        V9 
      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 
5                                                          The SAS System                         09:02 Saturday, September 20, 2025

      Physical Name: /usr/local/SAS/SASUsers/LabRet/Credit_Scoring_Users
NOTE: Library DIR_CB does not exist.
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
153        
154        
155        %LET _CLIENTTASKLABEL=;
156        %LET _CLIENTPROCESSFLOWNAME=;
157        %LET _CLIENTPROJECTPATH=;
158        %LET _CLIENTPROJECTPATHHOST=;
159        %LET _CLIENTPROJECTNAME=;
160        %LET _SASPROGRAMFILE=;
161        %LET _SASPROGRAMFILEHOST=;
162        
163        ;*';*";*/;quit;run;
6                                                          The SAS System                         09:02 Saturday, September 20, 2025

164        ODS _ALL_ CLOSE;
165        
166        
167        QUIT; RUN;
168

Patrick

It appears that libname teradata is pre-assigned. If you've got SMC or SAS DIS then you could look into the library metadata definition. You can also run libname teradata list; to get more information.

I personally prefer using a libname over your first way because it can be used directly within any Proc or data step. But of course sometimes it's advantageous to use explicit teradata SQL that allows you to better reduce data volumes on the Teradata side. The following is a variant to your first way but uses the libname definition.

libname mytera teradata .....;
proc sql;  
connect using mytera;
create table WAY1  as 
select * from connection to mytera
(
select top 5 *
from VBM374_USED_BRANCH_CUSTOMER
);
disconnect from mytera;
quit ;

In your sample case where the libref is teradata it would of course be connect using teradata; and you also wouldn't need the libname statement because the library is already assigned.

Tom

I don't see any notes in your example LOG that show a libref being made that connects to a Teradata database. If you have a libref named TERADATA is was defined somewhere else.

But you don't need to know how the libref was defined to use it in your SQL. You just need to know its name.

So if the libref's name is TERADATA you first code becomes:

proc sql;  
connect using TERADATA ;
create table WAY1  as 
select * from connection to TERADATA 
(
select top 5 *
from VBM374_USED_BRANCH_CUSTOMER
);
quit ;

If the libname is something else just use that name instead in both places.

PS I never understand why people bother issue DISCONNECT statement when they are going to close PROC SQL immediately anyway.

LinusH

"How can I know what is the libaname code that connect between sas and tera here???

I dont need to run the libaname in order to run it but I think that in background it is running but I dont know what libname is running ."

Do you want to know specifically what Terdata instructions the libname statement triggers?

Try this before your libname:

options sastrace="d,,,ds" sastraceloc=saslog nostsuffix msglevel=i;

If you are interested what SQL is sent down, you can settle for:

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

Data never sleeps

sas to tera

Re: sas to tera

Re: sas to tera

Re: sas to tera

The 2025 SAS Hackathon has begun!

SAS Training: Just a Click Away