topic Help with: WITH and CAST Functions in SAS Enterprise Guide

Help with: WITH and CAST Functions

jen123 — Fri, 06 Dec 2013 21:23:40 GMT

I was given a program (written by someone else) to run. My SAS/SQL skills are beginner-intermediate. I have never worked with WITH and CAST functions before. When I tried to run this program, it would take hours until it times out. I put the option to bring back only 10 OBS - theoretically it shouldn't take long.

Thoughts? It seems like the program is running in loops

OPTIONS OBS=10;

OPTIONS COMPRESS=YES;

proc sql;

connect to teradata(database=xyz tdpid=edwprod user=xxxx password="xxxx");

create table agent as select * from connection to teradata (

WITH ahist (contact_id, duration, account_id, per_num) as

(

SELECT distinct

ahist.contact_id,

ahist.duration,

ahist.account_id,

ahist.per_num

FROM acct_data ahist

where ahist.per_num = 6368

)

SELECT

ecm.cl_tid,

ahist.account_id,

ahist.duration,

agent.ecmcontact_id,

agent.per_num,

agent.user_role_dsc,

agent.start_ts,

artf.create_ts,

note.history_key_nm,

cast(agent.start_ts as date) as emp_date

FROM ecmcontact agent

join ahist

on ahist.contact_id=agent.ecmcontact_id

and ahist.per_num=agent.per_num

left outer join ecm_account ecm

on ecm.account_id=ahist.account_id

left outer join ecmartifact artf

on ahist.contact_id=artf.contact_id

and ahist.per_num=artf.per_num

left outer join citiecmnote note

on ahist.per_num=note.per_num

and note.ecm_artifact_id = artf.ecmartifact_id

ORDER BY ecm.cl_tid, artf.create_ts

);

quit;

Re: Help with: WITH and CAST Functions

Reeza — Fri, 06 Dec 2013 21:33:49 GMT

You're using something called SQL Pass Through, which means the syntax after the section below must be valid Terabase SQL. I don't know enough about Terabase to help, but you may be better off in posting to a Terabase forum or checking those out at least. WITH looks like a sub query to me, so you could potentially rewrite the code very easily, but I'd test it thoroughly.

create table agent as select * from connection to teradata (

This may work, if I understand the above correctly, which I'm not sure I do:

OPTIONS OBS=10;

OPTIONS COMPRESS=YES;

proc sql;

connect to teradata(database=xyz tdpid=edwprod user=xxxx password="xxxx");

create table agent as select * from connection to teradata (

SELECT

ecm.cl_tid,

ahist.account_id,

ahist.duration,

agent.ecmcontact_id,

agent.per_num,

agent.user_role_dsc,

agent.start_ts,

artf.create_ts,

note.history_key_nm,

cast(agent.start_ts as date) as emp_date

FROM ecmcontact agent

join (

SELECT distinct

ahist.contact_id,

ahist.duration,

ahist.account_id,

ahist.per_num

FROM acct_data ahist

where ahist.per_num = 6368) ahist

on ahist.contact_id=agent.ecmcontact_id

and ahist.per_num=agent.per_num

left outer join ecm_account ecm

on ecm.account_id=ahist.account_id

left outer join ecmartifact artf

on ahist.contact_id=artf.contact_id

and ahist.per_num=artf.per_num

left outer join citiecmnote note

on ahist.per_num=note.per_num

and note.ecm_artifact_id = artf.ecmartifact_id

ORDER BY ecm.cl_tid, artf.create_ts

);

quit;

Re: Help with: WITH and CAST Functions

TomKari — Sat, 07 Dec 2013 00:47:52 GMT

I can help out with the CAST part at least; in SQL, that's how data conversions are done. So I'm fairly sure your "cast(agent.start_ts as date) as emp_date" is just converting agent.start _ts into whatever Teradata's internal date format is, from where I assume it will be transformed into SAS's date format.

Tom

Re: Help with: WITH and CAST Functions

Tom — Sat, 07 Dec 2013 01:29:28 GMT

My Teradata programming friends tell me that you get better performance using GROUP BY instead of SELECT DISTINCT.

So your first sub-query could be re-written as:

SELECT ahist.contact_id

, ahist.duration

, ahist.account_id

, ahist.per_num

FROM acct_data ahist

WHERE ahist.per_num = 6368

GROUP BY 1,2,3,4