Multiple select in sas sql passthrough

[nid197]

Hi,
Can anyone help me with below select query.writting the sample query due to internet access issues at workplace

Proc sql,
Connect to teradata as tera(server"eiwb." Authdomain="sgkac." Mode=teradata)
Create table abc as select * from connection to teradata(
Select
Y.accno,
Y.transactionky,
Case when()
Case when()
..........
from
(
Select X.accno,
X.transactionky,
Count(X.transcount) as transcnt,
Log(acd) as acd2,
Log(brd) as brd2,
From(
Select a.transactionky key,b.accno,
Max(gsj) as jsh,
Log.........
from
Kcseg.cashflow a join
Kcseg.accountdm b
On a.accountno=b.accountno
Where accountno in (527828,6272929,5262828) and a.datekey between 20190801 and 20190831
Group by
b.accountno,a.transactionky
)x
Group by accountno,transactionky
)y
)by tera;
Disconnect from tera;
Quit;

Please help if i have it the right way.because the query is giving me results in teradata but using sas passthrough it is giving syntax error 22-322:

[nid197]

Here there is no option of uploading images.if someone can help me with this i can email a picture of a query.Thanks!

[Kurt_Bremser]

Only use pictures for something that is in fact a picture. Code has to be posted with the "little running man" icon, logs and other textual data (or non-SAS code, like shell scripts) with the {i} button.

Such data or code is best copy/pasted into the subwindow.

Pictures can be posted with the "Photos" button.

[Tom]

You made a connection named TERA and then are trying to use a connection named TERADATA in your SAS side SELECT statement.  You also included a spurious BY TERA at the end of your CREATE TABLE statement.

Your code should follow this format.

connect to Y  ... ;
select ... from connection to Y (....);

Or if you want to rename the connection use:

connect to Y as X ... ;
select ... from connection to X (....);

The documentation on the EXECUTE statement has that BY X syntax at the end:

execute (....) by X;

But someone pointed out recently that it is possible to move the connection name to the front. Like this:

execute by X (....);

For this type of question it is best to show the lines from the log where SAS has tagged the confusing code. 

Remember to use the Insert Code button on the menu bar of the edit form (looks like {i}) to get a pop-up window to paste the lines so that the formatting is preserved.

[Kurt_Bremser]

Proc sql,
Connect to teradata as tera(server"eiwb." Authdomain="sgkac." Mode=teradata)
Create table abc as select * from connection to teradata(

Three immediate issues:

Comma instead of semicolon to end the proc sql statement.

No semicolon to end the connect statement

Missing equals sign in server"eiwb.".

[nid197]

Yes sorry for the tpying mistake
1.there is a semicolon after connect
2.there is a semicolon at the end
3.there is a equal sign in eiwb statement

[Krueger]

Using SQL Passthrough you should be able to copy paste your working SQL code into the passthrough statement. Is your SQL code working on it's own in it's respective DBMS?

[nid197]

Yes it is working and giving me desired results in teradata

[nid197]

Here is a screenshot of the code I am sharing.please let me know if you see any error.

Sorry i cant post the code

[ChrisNZ · Accepted solution]

Please read the comments above! Why bother asking questions otherwise?

As stated:

1. Do not paste code as images

2. You have an extraneous  by TERA   after the select query. This is invalid syntax.

You'll notice that this happens to be underlined in the log. It is not a coincidence.

3. Use the log. It's here to help you.

[SASKiwi]

@nid197  - Copying and pasting code will avoid typos.

[ChrisNZ]

Is your question answered? If so mark the thread as solved. Thank you.