Join Teradata table to SAS local table

frankg80 — Mon, 10 Aug 2020 21:58:20 GMT

Hi, I'm pretty new to SAS EG. I am trying to join a table from teradata to a table I created in my work library called Work.jobs. Below is my code but I get an error. I put the error below my code. Orginianlly I was going to try and connect the teradata table to a sequel server table but I wasn't sure if that was possible. So intead, I created my work.jobs table from sequel server so that I can join it from my local folder to teradata instead.

proc sql;
connect to teradata(user="&user.@LDAP" tdpid=whlsp pass="&password.");
drop table work.hr;
create table work.hr as
select * from connection to teradata (
select a.*
from WDM_S_HR_NPRIV_S0.VW_CP_PROFILE_PUB a
join work.jobs b on a.emplid=b.Job_Owner_empid

);
disconnect from teradata;
quit;

Error:

43 proc sql;
44 connect to teradata(user="&user.@LDAP" tdpid=whlsp pass="&password.");
45 drop table work.hr;
NOTE: Table WORK.HR has been dropped.
46 create table work.hr as
47 select * from connection to teradata (
48 select a.*
49 from WDM_S_HR_NPRIV_S0.VW_CP_PROFILE_PUB a
50 join work.jobs b on a.emplid=b.Job_Owner_empid
51
52
53 );
ERROR: Teradata prepare: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword
or '(' between the 'join' keyword and the 'work' keyword. SQL statement was: select a.* from
WDM_S_HR_NPRIV_S0.VW_CP_PROFILE_PUB a join work.jobs b on a.emplid=b.Job_Owner_empid.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
54 disconnect from teradata;
NOTE: Statement not executed due to NOEXEC option.
55 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.42 seconds
cpu time 0.01 seconds

topic Join Teradata table to SAS local table in SAS Enterprise Guide

Join Teradata table to SAS local table