topic Re: sql server-WITH (NOLOCK) -code in SAS in SAS Programming

sql server-WITH (NOLOCK) -code in SAS

Ronein — Tue, 09 Apr 2024 10:10:03 GMT

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.

Re: sql server-WITH (NOLOCK) -code in SAS

yabwon — Tue, 09 Apr 2024 10:18:25 GMT

Did you try:

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

Re: sql server-WITH (NOLOCK) -code in SAS

Patrick — Tue, 09 Apr 2024 10:36:21 GMT

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;

Re: sql server-WITH (NOLOCK) -code in SAS

Ronein — Tue, 09 Apr 2024 10:49:56 GMT

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

Re: sql server-WITH (NOLOCK) -code in SAS

Ronein — Tue, 09 Apr 2024 10:51:14 GMT

You solution is perfect.

I have another question-

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

Re: sql server-WITH (NOLOCK) -code in SAS

yabwon — Tue, 09 Apr 2024 11:00:08 GMT

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-2017/

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

Re: sql server-WITH (NOLOCK) -code in SAS

Patrick — Tue, 09 Apr 2024 23:43:03 GMT

@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;

Re: sql server-WITH (NOLOCK) -code in SAS

Ronein — Wed, 10 Apr 2024 01:53:17 GMT

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;