Thanks, it ran but I then got the below privileges error.   I'll see about getting my access changed, but in the meantime, is there another way to do this?   Thanks   NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-01031: insufficient privileges.. ... View more

Thanks SASKiwi,   I've run this code:   Libname line was successful; NOTE: Libref ORALOAD was successfully assigned as follows:   LIBNAME oraload ORACLE user=XXXXXXXX pw=XXXXXXX path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXXXXXXXXXX.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=XXXXXXXX)))"; connection = global dbmstemp = yes insertbuff = 10000; data oraload.mytemptable; set WORK.CUST_IDS; RUN;   I got the below error at the connection line;   29 connection = global dbmstemp = yes insertbuff = 10000; __________ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. Is there something needed in the CONNECTION statement?   Thanks ... View more

Thanks Tom & SASKiwi,   I'm still stuck as I'm getting the same errors previously described.Thanks anyway for your time.  ... View more

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 ... View more

Hi Damo,   It's strange but the word CONNECT is coloured Orange?   If I remove the ';' after 'proc sql noprint' it goes back to black as it should be?   Any ideas? ... View more

Thanks ballardw,   The code submitted is in the order I posted.   such as; libname mylib oracle user=XXXXXX pw=XXXXXX path="(DESCRIPTION= (ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXXXXXXX.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=XXXXXX)))"; Any ideas as what may be wrong otherwise?   Thanks ... View more

Thanks Damo,   I've tried to adapt this to my settings, which currently work in other queries, but I get a   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; create table TABLE1 as select * from connection to mylib ( select DISTINCT ID FROM DBC.TABLE2 WHERE ROWNUM < 50 ) ;QUIT; The error is below but doesn't make sense as my credentials are correct?   ERROR: The MYLIB engine cannot be found. ERROR: A Connection to the mylib DBMS is not currently supported, or is not installed at your site. ... View more

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 ... View more

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 ... View more

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 ; ... View more

Sorry made an error in the ref to the first query, ora.table1 should be ora.IDS ... View more

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; ... View more

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.     ... View more

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. ... View more