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;
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.