Using SAS datasets in a PROC SQL EXECUTE

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Using SAS datasets in a PROC SQL EXECUTE

[ Edited ]

Hi,

 

I have a PROC SQL execute statement calling an ORACLE Merge, using which I update an Oracle table.

 

Since this is pass-through, I expect we cannot in any way use a SAS dataset in this Merge. Is this right?

 

Code -

 

PROC SQL;
CONNECT TO ORACLE(user=<user> pass=<pwd> path=<path>  );

EXECUTE(

MERGE INTO schema1.table1 A
USING <SAS dataset> B
ON ( A.Col1 = B.Col1 )
WHEN MATCHED THEN
UPDATE SET
A.Col2 = B.Col2
,A.Col3 = datetime()
) BY ORACLE;


Accepted Solutions
Solution
‎12-19-2016 11:04 AM
Super User
Posts: 6,963

Re: Using SAS datasets in a PROC SQL EXECUTE

You are right. Inside the execute you can only access Oracle data and use Oracle syntax.

If you need interaction with SAS data, either pull data to SAS and merge/update/whatever there, and push the result back into Oracle, or you can create a delta table, load it into Oracle, and then use the execute to work with it there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 17,907

Re: Using SAS datasets in a PROC SQL EXECUTE

You only can use datasets on the server. 

SAS functions (datetime() is Oracle?) are also invalid. 

 









I have a PROC SQL execute statement calling an ORACLE Merge, using which I update an Oracle table.

 

Since this is pass-through, I expect we cannot in any way use a SAS dataset in this Merge. Is this right?

 

 


In any way? 

 

Depends on what your trying to do. Macro lists often come in handy here. 

Solution
‎12-19-2016 11:04 AM
Super User
Posts: 6,963

Re: Using SAS datasets in a PROC SQL EXECUTE

You are right. Inside the execute you can only access Oracle data and use Oracle syntax.

If you need interaction with SAS data, either pull data to SAS and merge/update/whatever there, and push the result back into Oracle, or you can create a delta table, load it into Oracle, and then use the execute to work with it there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,260

Re: Using SAS datasets in a PROC SQL EXECUTE

No. Not directly.

You could upload your SAS table to an Oracle (temporary?) table first, then an execute would probably work.

Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 233 views
  • 1 like
  • 4 in conversation