BookmarkSubscribeRSS Feed
OscarBoots2
Calcite | Level 5

Here's my latest & i'm getting an 'expecting a name error';

 

proc sql noprint;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" 
user=xxxxxxxx pw=xxxxxxxx);

execute(create table IDS as 

select DISTINCT 
ID

FROM table_1

WHERE ROWNUM < 50

by ora);

create table MORE_IDS as select * from connection to ora
(
select DISTINCT 
id

from IDS A
INNER JOIN TABLE_2 B
ON A.ID = B.ID

);
disconnect from ora;
quit;

And here's the log script;

 

                                                         The SAS System                               14:24 Sunday, March 11, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         


25         proc sql noprint;
26         connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
27         (HOST = xxxxxxxx.com)(PORT = 1234))
28         (CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))"
29         user=xxxxxxxxx pw=XXXXXXXXXX);
30         
31         execute(create table IDS as
32         
33         select DISTINCT
34         ID
35         
36         FROM table_1
37         
38         WHERE ROWNUM < 50
39         
40         by ora);
                  _
                  22
ERROR 22-322: Expecting a name.  
Tom
Super User Tom
Super User

For SAS to understand what you are trying to do you need to follow the SYNTAX PATTERN that the commands expect.

 

The pattern for an EXECUTE statement.is looking for

execute (SQL STATEMENTby DBNAME ;

 

Your command:

execute

(create table IDS as select DISTINCT  ID FROM table_1 WHERE ROWNUM < 50 by ora

);

 

Notice how your command has deviated from the pattern by putting the BY ORA inside the parentheses. This causes two errors. SAS is not seeing the expected BY XXXX after the remote sql command.  And if it did succeed in getting passed to the remote database then that wouldn't understand the BY ORA at the end.  It would be looking for proper SQL syntax there, so perhaps a "group by" or an "order by", but not just a bare "by".

 

 

OscarBoots2
Calcite | Level 5

Thanks Tom, that gives me the insufficient privileges error I was getting before.

 

I think I'll use the other syntax other than the execute version.

 

 

OscarBoots2
Calcite | Level 5

OK, using this script I get the 'Table does not exist error.

 

How do I connect to the table I just created in ora?

 

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxxxx pw=xxxxxx);
         
create table IDS as select * from connection to ora
(
select DISTINCT 
ID

FROM table_1

WHERE ROWNUM < 50

)
;QUIT;

PROC SQL;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" 
user=xxxxxxxx pw=xxxxxxxxx);
 
create table TABLE_2 as select * from connection to ora
(
select DISTINCT 
A.ID;
B.SALES

from ora.table1 A    ------- HOW DO I ACCESS THIS TABLE?
INNER JOIN dbc.table_3 B
ON A.ID = B.ID
);
         
Disconnect from ora ;
quit;
OscarBoots2
Calcite | Level 5
Sorry made an error in the ref to the first query, ora.table1 should be ora.IDS
OscarBoots2
Calcite | Level 5

Can anyone advise the full libname statement using the my string below;

 

This how I've been connecting,

 

connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))
user=xxxxxxx pw=xxxxxxxx);

 

but I need to convert this into the below syntax;

 

libname mylib oracle .... connection=global ;
OscarBoots2
Calcite | Level 5

Hi Tom,

 

I think your answer of using a MyLib statement could work but I need to know how I can use my credentials to do this?

 

As you have suggested this solution, can you tell me how to change my connection details to a LibName statement OR is it not a possibility?

 

Thanks

SASKiwi
PROC Star

Thanks Tom, that gives me the insufficient privileges error I was getting before.

I think I'll use the other syntax other than the execute version

 

I think you are barking up the wrong tree. An "insufficient privileges error" means that your user account does not have the right permissions to create tables in the database you are querying. It doesn't matter whether you use the CONNECT statement or a LIBNAME statement you will get the same result. It is time to talk to your friendly Oracle database administrator to find out how temporary tables are managed at your site. He (or she) may even need to change the permissions on your user account for you to get this to work.

 

This SAS Note may also be helpful: http://support.sas.com/kb/13/182.html

OscarBoots2
Calcite | Level 5

Thanks but it's the 'execute ....' query that is giving me the 'insufficient privileges' error.

 

So what I want to know is how to write the connection string using my credentials with the Libname statement.

 

Can you help with this?

 

Thanks

SASKiwi
PROC Star

@Tom already gave you the answer earlier in this post. If you combine that with what you used in your other post:

 

libname mylib oracle user=XXXXXX pw=XXXXXX  
path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) 
	(HOST = XXXXXXXXXXX.com)(PORT = 1234))
	(CONNECT_DATA = (SERVICE_NAME=XXXXXX)))";


proc sql noprint;
connect using mylib ;
execute 
(create .....
) by mylib;
quit;

 

OscarBoots2
Calcite | Level 5

Thanks SASKiwi but the above script shows the word 'connect' in orange?

 

I can't find any syntax issues, maybe you can paste this into EG & see what's up?

 

Thanks

Tom
Super User Tom
Super User

Don't depend on the color highlighting of an editor.

Run the code and read the log.

SASKiwi
PROC Star

EG colour-codes this incorrectly. It should still work.

OscarBoots2
Calcite | Level 5

Thanks Tom & SASKiwi,

 

I'm still stuck as I'm getting the same errors previously described.Thanks anyway for your time. 

SASKiwi
PROC Star

If you are referring to the "insufficient privileges" error while trying to create an Oracle temporary table, then you need to get advice from your Oracle administrator on the correct way to do this on your database and that may include getting your Oracle account permissions changed. It is not an error you can code around by connecting to the database a different way. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 29 replies
  • 1693 views
  • 2 likes
  • 3 in conversation