BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

There is very big table in TeraData and I need to create a query that use this table.

The query is running on SAS and based on SAS data set and the table in Tera.

In real the table name in Tera is  Tera.Tera_tbl  (But for the code written here I wrote only tera_tbl).

The target is to recognize artificial loans.

The criteria for it is If the loan key has value M14 or M15 (in tera table) then it is artificial loan.

What id better way ? Way1 or Way2?

Or maybe you can show better way to do it?

 


Data Have;
input Agreement_Account_Id;
cards;
623977145
623570095
111111111
222344444
;
run;

Data Tera_tbl;
input Event_Category_Type_Code $ Agreement_Account_Id;
cards;
M14 623570095
R1 623977145
M0 623570095
M14 623977145
M0 623977145
;
Run;


proc sql;
create table Way1 as
select  a.Agreement_Account_Id,
         max(case when a.Event_Category_Type_Code IN('M14','M15') then 1 else 0 end ) as Ind_artificial_Haamada label='אינדקיטור העמדה מלאכותית'
from Tera as a
inner join  Have  as b
on a.Agreement_Account_Id=b.Agreement_Account_Id
group by a.Agreement_Account_Id
;
quit;


proc sql;
create table Way2 as
select  distinct a.Agreement_Account_Id
from Tera(keep=Event_Category_Type_Code Agreement_Account_Id Where=(Event_Category_Type_Code IN('M14','M15'))) as a
inner join  Have  as b
on a.Agreement_Account_Id=b.Agreement_Account_Id
;
quit;
 

 

7 REPLIES 7
Patrick
Opal | Level 21

If your Teradata table is big and your SAS table small then try and push processing to Teradata. I order to do so you need either to load your SAS table first into Teradata (could be a temporary table) or you need to generate SAS code that creates a where clause with an in operator that just contains all the key values from your small SAS tables (small means here not more than 100000 rows).

 

You could try if DBKEY does the job for you SAS Help Center: DBKEY= Data Set Option

Patrick_0-1695885750531.png

If DBKEY doesn't solve the problem for you then have a look into this very similar discussion.

 

Ronein
Meteorite | Level 14

Thanks, However I get error 

ERROR: Invalid option name DBKEY.

This is my code I run


proc sql;
create table tbl_artificial_Haamadot as
select  distinct a.Agreement_Account_Id
from TeraData.V0500_1_FINANCIAL_EVENT(keep=Event_Category_Type_Code Agreement_Account_Id Where=(Event_Category_Type_Code IN('M14','M15'))) as a
inner join  Haamadot_DWH_9(dbkey=(Agreement_Account_Id))  as b
on a.Agreement_Account_Id=b.Agreement_Account_Id
group by a.Agreement_Account_Id
;
quit;
Patrick
Opal | Level 21

According to the example in the docu looks like the DBKEY options needs to be on the database table.

 

Patrick_0-1695893726962.png

What doesn't make much sense to me is the use of SAS table options like keep on a database table.

See if below code works for you.

proc sql;
  create table tbl_artificial_Haamadot as
    select  distinct a.Agreement_Account_Id
      from TeraData.V0500_1_FINANCIAL_EVENT(dbkey=(Agreement_Account_Id)) as a
        inner join  Haamadot_DWH_9 as b
          on a.Agreement_Account_Id=b.Agreement_Account_Id
        where a.Event_Category_Type_Code IN ('M14','M15')
  ;
quit;

I can't test it so I'm not sure if the DBKEY option will have the desired effect for above code because of the already existing where clause.

 

 

Tom
Super User Tom
Super User

Of course the DB_KEY= option goes on the database table.   You are telling SAS to use those variables as the KEY variables for that table so that it does not have to pull the whole table over to SAS before trying to join.  It should allow SAS to instead upload the key values from the SAS dataset to the remote database and so only retrieve the observations from the database it needs.

Ronein
Meteorite | Level 14
They are stored as numeric and there are 50,000 distinct values . Please note that the tera data table is very very big ( maybe 500 millions rows) and in the tera table each agreement_acvount_id can have multiple rows
Kurt_Bremser
Super User

Create code like this:

filename code temp;

data _null_;
file code;
set have end=done;
if _n_ = 1
then do;
  put 'proc sql;';
  put 'create table want as select agreement_account_id';
  put 'from have where agreement_account_id in (';
  put 'select agreement_account_id from tera where Event_Category_Type_Code in ("M14","M15") and agreement_account_id in (';
end;
if _n_ > 1 then put ',';
put quote(put(agreement_account_id,z10.)); * use suitable format here;
if done then do;
  put ')); quit;'
end;
run;

%inc code;

Untested, for obvious reasons. I would hope that SAS can translate the sub-select and push it to Teradata.

If not, create explicit pass-through code in a similar manner.

sas-innovate-wordmark-2025-midnight.png

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.

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
  • 7 replies
  • 1229 views
  • 1 like
  • 4 in conversation