sql server-WITH (NOLOCK) -code in SAS

[Ronein]

Hello

There is a code that run in sql server 

select  *
FROM   Application
WITH  (NOLOCK) 

I want to run this code in SAS (There is a library called enginal where can see the tables in sql server)

What is the way to run it in sas?

proc sql;
create table Application as
select *
FROM enginal.Application
WITH (NOLOCK)
;
quit;
 

Error

                _
                22
                76
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.  

ERROR 76-322: Syntax error, statement will be ignored.

[yabwon]

Did you try:

proc sql;
create table Application as
select *
FROM enginal.Application
;
quit;

?

[Ronein]

Sure but you didnt add "With No LOCK" and didnt add equivalent SAS code to  "WITH NO LOCK"

[yabwon]

1) I asked did you execute "plain"?

proc sql;
create table Application as
select *
FROM enginal.Application
;
quit;

You didn't answer.

2) It's hard to add something that does no exist...

The "with NOLOCK" is SQL Server specific..

a) https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2...

b) https://stackoverflow.com/questions/686724/what-is-with-nolock-in-sql-server

[Ronein]

I have executed your code.it is the regular query. I think the code provided here answer the question
proc sql;
create table Application as
select *
FROM enginal.Application(read_lock_type=nolock)
;
quit;

[Patrick · Accepted solution]

WITH NOLOCK() is not SAS SQL syntax flavour. One option is to use explicit SQL passthrough as then you can just have SAS to send the SQL Server syntax as-is to the DB for execution.

proc sql;
  connect using enginal;
  select * from connection to enginal
    ( select * FROM Application WITH (NOLOCK) )
    ;
quit;

[Ronein]

You solution is perfect.

I have another question-

Is there a SAS code that  do same like "With No Loc(sql server)?

[Patrick]

---

@Ronein wrote:

You solution is perfect.

I have another question-

Is there a SAS code that  do same like "With No Loc(sql server)?

---

I normally look into the SAS docu for the access engine to answer such questions. If using the docu you would find READ_LOCK_TYPE= Data Set Option

READ_LOCK_TYPE=ROW | PAGE | TABLE | NOLOCK | VIEW

proc sql;
  create table Application as
  select *
  FROM enginal.Application(read_lock_type=nolock)
  ;
quit;