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;
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
If DBKEY doesn't solve the problem for you then have a look into this very similar discussion.
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;
According to the example in the docu looks like the DBKEY options needs to be on the database table.
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.
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.
How many distinct agreement_account_id does your SAS dataset have?
In reality, are these keys stored as character or numeric?
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.
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.
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.
Ready to level-up your skills? Choose your own adventure.