topic Re: Multiple select in sas sql passthrough in SAS Programming

Multiple select in sas sql passthrough

nid197 — Wed, 27 Nov 2019 14:47:58 GMT

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:

Re: Multiple select in sas sql passthrough

nid197 — Wed, 27 Nov 2019 14:51:54 GMT

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

Re: Multiple select in sas sql passthrough

Kurt_Bremser — Wed, 27 Nov 2019 14:57:14 GMT

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.

Re: Multiple select in sas sql passthrough

Tom — Wed, 27 Nov 2019 15:05:17 GMT

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.

Re: Multiple select in sas sql passthrough

Kurt_Bremser — Wed, 27 Nov 2019 14:59:48 GMT

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.".

Re: Multiple select in sas sql passthrough

nid197 — Wed, 27 Nov 2019 15:11:36 GMT

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

Re: Multiple select in sas sql passthrough

Krueger — Wed, 27 Nov 2019 15:59:06 GMT

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?

Re: Multiple select in sas sql passthrough

SASKiwi — Wed, 27 Nov 2019 19:13:51 GMT

@nid197 - Copying and pasting code will avoid typos.

Re: Multiple select in sas sql passthrough

nid197 — Thu, 28 Nov 2019 09:07:12 GMT

Yes it is working and giving me desired results in teradata

Re: Multiple select in sas sql passthrough

nid197 — Thu, 28 Nov 2019 09:38:48 GMT

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

Re: Multiple select in sas sql passthrough

ChrisNZ — Thu, 28 Nov 2019 11:30:15 GMT

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.

Re: Multiple select in sas sql passthrough

ChrisNZ — Thu, 28 Nov 2019 23:09:25 GMT

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