Two Different Environments?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Two Different Environments?

Hi SAS community,

A project that I'm working on requires an extract of member_ids...  and then inserting these chosen members_ids in a second piece of code.

The reason why the second piece of code is different, is because it performs a complex step and i'm inheriting it from another teammate who just wrote it.

Part 2 code works on it's own (for one member_id), but i'd like to upload 10,000 member_id's on my permanent table in part 1 and pass them along to the Part 2 code.

Issue: The Environments are Different, I think? My connect in part 1 isn't the same as the connect in part 2.

It also depends on which code I try running first.

If I run Part 1, then Part 2 errors (saying that there is some issue with one of the volatile tables already being created).

If I run Part 2 first (with one sample member_ID - there are no errors) and then want to comment out that single member and go back to run Part 1, then Part 1 has an error about not being able to run something other than an execute (commit)...

Anyone have any thoughts that of things that I might try to resolve this issue?

See the two Code Connects below.

Part 1: MY permanent table connect.

[This only shows the deleting of the permanent table, but there’s an upload portion that comes a bit later on…]

LIBNAME TERALIB TERADATA USER="&username.@LDAP" PASSWORD=&pass   

database=USERDATA_ENT

mode=teradata logdb=USER_UTLTY_ENT  TDPID= DWPROD2

CONNECTION=GLOBAL TPT=NO;

PROC SQL EXEC FEEDBACK STIMER;

CONNECT TO TERADATA AS TD1 (USER="&username.@LDAP" PASSWORD=&pass.

TDPID=DWPROD2 MODE=TERADATA CONNECTION=GLOBAL);

EXECUTE(

delete from MY_PERMANENT_TABLE ALL

) BY TD1

;

quit;

....

then MY_PERMANENT_TABLE is uploaded in the next step with this connect.

Part 2 of Project… Use Permanent Table within the heart of this code connect.

[The "TERADATAAUTH" was actually set up to run a similar "@LDAP" connection.]

     LIBNAME CLMLOAD TERADATA AUTHDOMAIN = TERADATAAUTH TDPID=DWPROD2 CONNECTION=GLOBAL DATABASE='USER_ID';

PROC SQL;

CONNECT TO TERADATA (AUTHDOMAIN = TERADATAAUTH CONNECTION=GLOBAL MODE=TERADATA TDPID=DWPROD2);

%PUT &SQLXMSG;

EXECUTE(

CREATE VOLATILE TABLE USER_ID.ICD9DIAG,

      NO LOG

      (

      DIAG_CD                             VARCHAR(10)

      )

UNIQUE PRIMARY INDEX(DIAG_CD)

ON COMMIT PRESERVE ROWS

) BY TERADATA;

....

code goes on.


Accepted Solutions
Solution
‎03-17-2014 04:37 PM
Occasional Contributor
Posts: 8

Re: Two Different Environments?

Hi Tom,

Actually, I found someone that knew. Turns out that I had to comment out "MODE=TERADATA"

and then add in the "execute (commit) by TD1;" when necessary.

Thanks for your response!


View solution in original post


All Replies
Super User
Super User
Posts: 6,500

Re: Two Different Environments?

Not sure what your question is, but depending on the connection mode you are required to manually submit the COMMIT statement to Teradata.

So it sounds like you just need to add this statement to PART 1.

execute (commit) by TD1 ;

Solution
‎03-17-2014 04:37 PM
Occasional Contributor
Posts: 8

Re: Two Different Environments?

Hi Tom,

Actually, I found someone that knew. Turns out that I had to comment out "MODE=TERADATA"

and then add in the "execute (commit) by TD1;" when necessary.

Thanks for your response!


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 357 views
  • 3 likes
  • 2 in conversation