topic Re: SQL to Proc SQL in SAS Programming

SQL to Proc SQL

Sathish_jammy — Thu, 07 Mar 2019 03:52:08 GMT

Hi all

Please refer the code given below in Proc SQL format

INSERT INTO School_Reg1
SELECT * FROM School_Reg2 a
WHERE a.student_id not in (select student_id from School_Reg1 where student_id =a.student_id )

While I tried Error occurred.

ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to
the data set. This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.

Re: SQL to Proc SQL

kiranv_ — Wed, 06 Mar 2019 12:39:03 GMT

you are using Reg1 in you where clause and inserting back in reg1, that is the problem. This is very common issue. You cannot read and write to same dataset at same time. Once you have where clause on Reg1, it will have read lock, so you cannot insert the records i.e. that is insert record.

Think of this as you are going to bank and depositing and taking out money at exactly at same time , it is simply not possible, one transaction has to completed after the another one can start. Your query is violating simple ACID principles. Look for consistency properties in https://en.wikipedia.org/wiki/ACID_(computer_science).

If possble create some temporary dataset and insert those records in your reg1.

Re: SQL to Proc SQL

Kurt_Bremser — Wed, 06 Mar 2019 12:44:03 GMT

READ YOUR LOG. In its entirety. That is EXTREMELY important. Really. I mean it.

When I run this sample code to re-create your situation:

data class;
set sashelp.class;
where name ne 'Alfred';
run;

proc sql;
insert into class
select * from sashelp.class a
where a.name not in (select name from class where name = a.name)
;
quit;

I get this log:

32         proc sql;
33         insert into class
34         select * from sashelp.class a
35         where a.name not in (select name from class where name = a.name)
36         ;
WARNING: This DELETE/INSERT statement recursively references the target table. A consequence of this is a possible data integrity 
         problem.
ERROR: You cannot reopen WORK.CLASS.DATA for update access with member-level control because WORK.CLASS.DATA is in use by you in 
resource environment SQL (2).
ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. 
       This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37         quit;

You simultaneously open the target dataset for update and read (because of your sub-select), and that is not possible without serious integrity repercussions. The WARNING and the first ERROR alert you to that.

Save your exclusion list to a helper table:

data class;
set sashelp.class;
where name ne 'Alfred';
run;

proc sql;
create table lookup as
select a.name
from class a inner join sashelp.class b
  on a.name = b.name
;
insert into class
select * from sashelp.class a
where a.name not in (select name from lookup)
;
quit;

and Bob's your uncle.

BIG HINT:

The last ERROR in your log is always the least important, until you have fixed all WARNINGs/ERRORs before it.

Debug your code from the top down.

Re: SQL to Proc SQL

RW9 — Wed, 06 Mar 2019 12:45:08 GMT

This question has been asked several times. Please use the search bar in future before posting.

E.g.

https://communities.sas.com/t5/General-SAS-Programming/UNDO-POLICY-NONE-update-warning-SAS-warnings-are-a-Nanny-state/td-p/283649

See the undo_policy option on the proc sql statment.

Alternatively, and more importantly, why not just use Base SAS? Unless your doing something to a database - and in that case do it on the database - there is no need to resort to SQL.

Re: SQL to Proc SQL

Sathish_jammy — Thu, 07 Mar 2019 08:30:36 GMT

Dear @Kurt_Bremser

Your code works well.

But I have a little issue,

Once I tried your method with replacement by my tables it performed well, but on few tables it cause ERROR.

%macro aaa(lu,Tab1,Tab2);

proc sql;
create table &lu as
select a.Student_id
from &Tab1 a inner join &Tab2 b
  on a.Student_id = b.Student_id
;
insert into &Tab1 
select * from &Tab2 a
where a.Student_id not in (select Student_id from &lu)
;
quit;
%mend;
%aaa (reg1,S0.School1,S2.School2);
%aaa (reg2,S0.School1,S3.School3);
%aaa (reg3,S0.School1,S4.School4);

For the First Macro line

88 %aaa(reg1,S0.School1,S2.School2);
NOTE: Table WORK.REG1 created, with 132489 rows and 1 columns.

NOTE: 55148 row was inserted into S0.School1

/*************************************************************/

88 %aaa(reg2,S0.School1,S3.School3);
NOTE: Table WORK.REG2 created, with 456891 rows and 1 columns.

NOTE: 8834 row was inserted into S0.School1

/************************************************************************************************/

While run the Reg3 Macro Line

NOTE: Table WORK.REG3 created, with 643391 rows and 1 columns.
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
ERROR: ROLLBACK issued due to errors for data set S0.School1.DATA.
NOTE: The SAS System stopped processing this step because of errors.

What is this EROOR mean to it.

I have no idea about an ERROR.. I google it for its description but I can't find it.

Could you please explain, what cause an ERROR.

Thanks in advance!

Re: SQL to Proc SQL

Kurt_Bremser — Thu, 07 Mar 2019 08:42:49 GMT

Welcome to the fun of adding an unnecessary level of complexity in your process (Micro$oft SQL Server).

Try to get help from MS SQL Server specialists
Move all data to SAS and process there
Move all data to SQL Server and process there, either directly in MS SQL or with explicit pass-through

I'm no expert at all with non-SAS SQL and foreign database systems, so I can't help you with that.

Re: SQL to Proc SQL

Sathish_jammy — Thu, 14 Mar 2019 07:03:55 GMT

Dear @Kurt_Bremser

I tried the another model in the same method but I failed.

Actually the code u suggested to get new names that works fine.

but What if a person have a same name but with different gender.

For example :

data class2;
input Name$ Sex$ Age Height Weight;
cards;
Joyce M 12 62.3 55.5
run;

In class - Joyce F

in class2 - Joyce M

I tried like :

proc sql;
create table lookup as
select a.name,a.sex
from class a inner join class2 b
  on a.name = b.name
;
insert into class 
select * from class2 a
where a.sex not in (select sexfrom &lu)

Please correct it and let me know where I'm wrong,

Thanks in advance!

Re: SQL to Proc SQL

Kurt_Bremser — Thu, 14 Mar 2019 07:29:12 GMT

To do this in SQL, you might need to create a new index variable concatenated from name and sex.

But in that case, I'd use data step programming, where such "compound joins" are much easier.