topic Re: Merge sas data set with teraData table in SAS Programming

Merge sas data set with teraData table

Ronein — Thu, 28 Sep 2023 07:08:15 GMT

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;

Re: Merge sas data set with teraData table

Patrick — Thu, 28 Sep 2023 07:25:01 GMT

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.

Re: Merge sas data set with teraData table

Ronein — Thu, 28 Sep 2023 07:49:31 GMT

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;

Re: Merge sas data set with teraData table

Patrick — Thu, 28 Sep 2023 09:46:41 GMT

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.

Re: Merge sas data set with teraData table

Kurt_Bremser — Thu, 28 Sep 2023 12:22:34 GMT

How many distinct agreement_account_id does your SAS dataset have?

In reality, are these keys stored as character or numeric?

Re: Merge sas data set with teraData table

Tom — Thu, 28 Sep 2023 15:05:19 GMT

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.

Re: Merge sas data set with teraData table

Ronein — Fri, 29 Sep 2023 08:58:02 GMT

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

Re: Merge sas data set with teraData table

Kurt_Bremser — Fri, 29 Sep 2023 13:26:18 GMT

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.