BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nid197
Obsidian | Level 7
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:
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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.

 

 

View solution in original post

11 REPLIES 11
nid197
Obsidian | Level 7
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
Super User

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
Super User Tom
Super User

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
Super User
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
Obsidian | Level 7
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
Pyrite | Level 9

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
Obsidian | Level 7

Yes it is working and giving me desired results in teradata

nid197
Obsidian | Level 7

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
Tourmaline | Level 20

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
PROC Star

@nid197  - Copying and pasting code will avoid typos.

ChrisNZ
Tourmaline | Level 20

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2175 views
  • 2 likes
  • 6 in conversation