Sas proc sql to teradata

[KamikazeBassi]

Hi all,

I am after suggestions on the following issue I have.  My systems allow me access to data on Teradata via SAS EG, but this gives me issues with joins when passing sql to Teradata. 

SAS Code:  Code is bring back data for customers from a list of say 10k customers for particular dates from a table that holds the accounts and dates required (tmp1).  The daily snapshot data is in tera.daily_snapshot_table

proc sql;

create table test1 as (

select a.acct, a.id_date, a.somedata1, a.somedata2

from tera.daily_snapshot_table  a

where a.acct in (select distinct acct from tmp1)

and a.id_date in (select distinct id_date from tmp1)

);

quit;

SAS is unable to join the tables together, so instead pulls everything down from the tera view and then does the extra filtering in SAS. This is what I have found very slow. Here is the actual SQL that is running in Teradata for your query, as you can see no reference to my accounts or snapshot dates and will return an estimated 291 million rows!!!  

SELECT "acct",

"ID_DATE",

CAST("somedata1" AS FLOAT),

CAST("somedata2" AS FLOAT),

FROM "TERA_VIEWS"."daily_snapshot_table";

I don't have access to Teradata directly and the DBA has told me what it is doing...but he unfortunately doesn't have SAS experience to fix this.

So what would be the best way to generate code that will pass though the values to Teradata to enable just the customers/dates I want to be returned?

many thanks

[SASKiwi]

When joining a Teradata table to a SAS table you have two choices. Either bring your Teradata data down to SAS and join with your SAS table in the SAS environment - this is what is happening now. The alternative is to upload your SAS table to Teradata and do your joining in the Teradata environment. Given your SAS table is small this is the better option but you would need permissions to create and update a temporary Teradata table. Your Teradata administrator should be able to set this up for you.

This SAS note describes how to load a SAS table into a temporary Teradata table: https://support.sas.com/kb/21/038.html

[ChrisNZ · Accepted solution]

A third option is to include the values to extract as part of the SQL.

Something like:

data _null_;
  call execute(
       'proc sql; '
     ||'create table TEST1 as '
     ||'select ACCT, ID_DATE, SOMEDATA1, SOMEDATA2 '
     ||'from TERA.DAILY_SNAPSHOT_TABLE '
     ||'where ACCT in ("DUMMY" ' );
  do until(LASTOBS);
    set UNIQUE_ACCT_LIST end=LASTOBS;
    call execute(','||quote(trim(ACCT)));
  end;
  call execute(') and DATE_ID in (-99 ' );
  do until(LASTOBS1);
    set UNIQUE_DATE_LIST end=LASTOBS1;
    call execute(catts(',', quote(put(DATE,date9.)), 'd' ));
  end;
  call execute('); quit;');
  stop;
quit;

Does this make sense?

[KamikazeBassi]

Hi

Not sure on this, how is it picking up my unique list of accounts and corresponding dates?

Do I replace the ("DUMMY" ' );  to a select of the accounts and dates? 

thanks

[KamikazeBassi]

ok.....understand what it is doing now,  just trying to run it....

[KamikazeBassi]

ok, that's great works really well!

just one thing...this brings back all data for accounts for all the dates?  if I have 75 distinct dates it is bringing back 75 rows for each account.

anyway of fixing this within this code? 

[KamikazeBassi]

Worked really well, much appreciated.

[SASKiwi]

@KamikazeBassi  - Just be aware that this works OK when your lookup table is relatively small. It won't work if you have millions of rows to select on as your WHERE clause will get too big.