SQLOBS behaviour (0 after delete)

[Maze]

Hi everyone,

I am having trouble to understand automatic variable SQLOBS' behaviour/assignment rules. I am deleting rows from a table on Oracle using a libname but SQLOBS is 0 afterwards even though there are rows beeing deleted. proc sql "feedback" option also does not show a delete statement but rather a "select distinct 1".

Can anyone tell me why SQLOBS is 0 even tough I am not using pass-through and why it works when deleting by SELECT instead but not when deleting by static value?

SAS Version is 9.04.01M8P022223

I have written a small example:

/*libname ORADB oracle path=...*/

*dummy data setup;
data ORADB.POC_DUMMY;
	length ID_COL $3. VAL_COL $6.;
	input ID_COL VAL_COL;
datalines;
ID1 VALUE1
ID2 VALUE1
ID3 VALUE2
;
run;

data WORK.DEL_ROW;
	ID_COL = 'ID3'; output;
run;

*delete, sqlobs will be 1 as expected (deletes 1 row);
proc sql feedback;
	delete from ORADB.POC_DUMMY
	where ID_COL in (
		select ID_COL from WORK.DEL_ROW
	);
quit;
%PUT NOTE: DEL by SEL &=SQLOBS;

*delete, sqlobs will be 0 (but it deletes 2 rows);
proc sql feedback;
	delete from ORADB.POC_DUMMY
	where VAL_COL = 'VALUE1';
quit;
%PUT NOTE: DEL by VAL &=SQLOBS;


*it did indeed delete 2 rows (cnt=0);
proc sql noprint;
	select count(*) into :cnt separated by '' from ORADB.POC_DUMMY;
quit;
%PUT NOTE: remaining rows &=cnt;

*cleanup;
proc sql;
	drop table ORADB.POC_DUMMY;
	drop table WORK.DEL_ROW;
quit;

And here is the log:

28         /*libname ORADB oracle path=...*/
29         
30         *dummy data setup;
31         data ORADB.POC_DUMMY;
32         	length ID_COL $3. VAL_COL $6.;
33         	input ID_COL VAL_COL;
34         datalines;

NOTE: The data set ORADB.POC_DUMMY has 3 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.96 seconds
      cpu time            0.16 seconds
      

38         ;
39         run;
40         
41         data WORK.DEL_ROW;
42         	ID_COL = 'ID3'; output;
43         run;

NOTE: Compression was disabled for data set WORK.DEL_ROW because compression overhead would increase the size of the data set.
NOTE: The data set WORK.DEL_ROW has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

44         
45         *delete, sqlobs will be 1 as expected (deletes 1 row);
46         proc sql feedback;
47         	delete from ORADB.POC_DUMMY
48         	where ID_COL in (
49         		select ID_COL from WORK.DEL_ROW
50         	);
NOTE: Statement transforms to:

        select distinct 1
          from ORADB.POC_DUMMY
         where POC_DUMMY.ID_COL in 
               ( select DEL_ROW.ID_COL
                   from WORK.DEL_ROW
               );

NOTE: Statement transforms to:

        delete
          from ORADB.POC_DUMMY
         where POC_DUMMY.ID_COL in 
               ( select DEL_ROW.ID_COL
                   from WORK.DEL_ROW
               );

NOTE: 1 row was deleted from ORADB.POC_DUMMY.

51         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.93 seconds
      cpu time            0.16 seconds
      

52         %PUT NOTE: DEL by SEL &=SQLOBS;
NOTE: DEL by SEL SQLOBS=1
53         
54         *delete, sqlobs will be 0 (but it deletes 2 rows);
55         proc sql feedback;
56         	delete from ORADB.POC_DUMMY
57         	where VAL_COL = 'VALUE1';
NOTE: Statement transforms to:

        select distinct 1
          from ORADB.POC_DUMMY
         where POC_DUMMY.VAL_COL = 'VALUE1';

58         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds
      

59         %PUT NOTE: DEL by VAL &=SQLOBS;
NOTE: DEL by VAL SQLOBS=0
60         
61         
62         *it did indeed delete 2 rows (cnt=0);
63         proc sql noprint;
64         	select count(*) into :cnt separated by '' from ORADB.POC_DUMMY;
65         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds
      

66         %PUT NOTE: remaining rows &=cnt;
NOTE: remaining rows CNT=0
67         
68         *cleanup;
69         proc sql;
70         	drop table ORADB.POC_DUMMY;
NOTE: Table ORADB.POC_DUMMY has been dropped.
71         	drop table WORK.DEL_ROW;
NOTE: Table WORK.DEL_ROW has been dropped.
72         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds
      

[yabwon]

I did test run on PostgreSQL, results are the same like on Oracle.

When I changed query to:

data WORK.DEL_ROW2;
	VAL_COL = 'VALUE1'; output;
run;
proc sql feedback;
	delete from ORADB.POC_DUMMY
	where VAL_COL 
  in (
		select DEL_ROW2.VAL_COL from WORK.DEL_ROW2
	);
quit;
%PUT NOTE: DEL by VAL &=SQLOBS;

I got 2 in the log:

664  data WORK.DEL_ROW2;
665    VAL_COL = 'VALUE1'; output;
666  run;

NOTE: The data set WORK.DEL_ROW2 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              390.50k
      OS Memory           20472.00k

667  proc sql feedback;
668    delete from ORADB.POC_DUMMY
669    where VAL_COL
670    in (
671      select DEL_ROW2.VAL_COL from WORK.DEL_ROW2
672    );
NOTE: Statement transforms to:

        select distinct 1
          from ORADB.POC_DUMMY
         where POC_DUMMY.val_col in
               ( select DEL_ROW2.VAL_COL
                   from WORK.DEL_ROW2
               );

NOTE: Statement transforms to:

        delete
          from ORADB.POC_DUMMY
         where POC_DUMMY.val_col in
               ( select DEL_ROW2.VAL_COL
                   from WORK.DEL_ROW2
               );

NOTE: 2 rows were deleted from ORADB.POC_DUMMY.

673  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.03 seconds
      memory              5487.96k
      OS Memory           25596.00k


674  %PUT NOTE: DEL by VAL &=SQLOBS;
NOTE: DEL by VAL SQLOBS=2

Looks "BUG-ish", I would report it to tech support.

Bart

[LinusH]

I think that SQLOBS reacts to the no of records from the select statment.

Have you tried SQLXOBS (still undocumented ASFAIK)?

[Maze]

You are probably right, but why doesnt the "feedback"-option show any kind of delete statement?
I tried SLQXOBS and it is also 0 for this case.

[LinusH]

From the documentation:
"The FEEDBACK option expands a SELECT * statement into a list of columns that the statement represents."

[Maze]

Yes but look at the different outputs in my log above. For the comparison-delete (first one) it shows a select and the delete statement. This is missing in the case I am questioning (second one).

[LinusH]

Gotcha, no, I can't explain that.

Another way to see statements executed in a target dbms is to:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

Unfortenately this doesn't work for my OLEDB engine with DELETE, but perhaps it works better for you?

51         proc sql;
52         	delete from oeldb.table
53         		where id = 5254;
SAS_SQL:  Unable to convert the query to a DBMS specific SQL statement due to an error. 

[Maze]

Thanks for supplying these options, the output is much more detailed!

Now one can see the actual delete statement and also a log message saying that 2 rows were deleted.

But still, SQLOBS is 0 afterwards.

What is curisous is

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

for the first statement, but not the second.

So maybe SQLOBS is only set correctly when a statement cant be directly executed against a db. But thats not documented I think and would be guesswork, no?

46         *delete, sqlobs will be 1 as expected (deletes 1 row);
47         proc sql feedback;
48         	delete from ORADB.POC_DUMMY
49         	where ID_COL in (
50         		select ID_COL from WORK.DEL_ROW
51         	);
 
ORACLE_5: Prepared: on connection 7
SELECT * FROM BILADM.POC_DUMMY
 
NOTE: Statement transforms to:

        select distinct 1
          from ORADB.POC_DUMMY
         where POC_DUMMY.ID_COL in 
               ( select DEL_ROW.ID_COL
                   from WORK.DEL_ROW
               );

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.
 
ORACLE_6: Prepared: on connection 9
SELECT * FROM BILADM.POC_DUMMY
 
NOTE: Statement transforms to:

        delete
          from ORADB.POC_DUMMY
         where POC_DUMMY.ID_COL in 
               ( select DEL_ROW.ID_COL
                   from WORK.DEL_ROW
               );

 
ORACLE_7: Prepared: on connection 9
SELECT  "ID_COL", ROWID FROM BILADM.POC_DUMMY 
 
 
ORACLE_8: Executed: on connection 9
SELECT statement  ORACLE_7
 
 
ORACLE_9: Prepared: on connection 9
DELETE  FROM BILADM.POC_DUMMY  WHERE  ROWID=:hv
 
 
ORACLE_10: Executed: on connection 9
DELETE statement  ORACLE_9
 
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
NOTE: 1 row was deleted from ORADB.POC_DUMMY.

ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
52         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.84 seconds
      cpu time            0.17 seconds
      

53         %PUT NOTE: DEL by SEL &=SQLOBS;
NOTE: DEL by SEL SQLOBS=1
54         
55         *delete, sqlobs will be 0 (but it deletes 2 rows);
56         proc sql feedback;
57         	delete from ORADB.POC_DUMMY
58         	where VAL_COL = 'VALUE1';
 
ORACLE_11: Prepared: on connection 7
SELECT * FROM BILADM.POC_DUMMY
 
NOTE: Statement transforms to:

        select distinct 1
          from ORADB.POC_DUMMY
         where POC_DUMMY.VAL_COL = 'VALUE1';

 
ORACLE_12: Executed: on connection 8
delete from BILADM.POC_DUMMY where BILADM.POC_DUMMY."VAL_COL" = 'VALUE1'
 
ORACLE: 2 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
59         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds
      

60         %PUT NOTE: DEL by VAL &=SQLOBS;
NOTE: DEL by VAL SQLOBS=0
61         %PUT NOTE: DEL by VAL &=SQLXOBS;
NOTE: DEL by VAL SQLXOBS=0
62         
63         
64         *it did indeed delete 2 rows (cnt=0);
65         proc sql noprint;
66         	select count(*) into :cnt separated by '' from ORADB.POC_DUMMY;
 
ORACLE_13: Prepared: on connection 7
SELECT * FROM BILADM.POC_DUMMY
 
 
ORACLE_14: Prepared: on connection 7
 select COUNT(*) from BILADM.POC_DUMMY TXT_1
 
 
ORACLE_15: Executed: on connection 7
SELECT statement  ORACLE_14
 
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data. 
67         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds
      

68         %PUT NOTE: remaining rows &=cnt;
NOTE: remaining rows CNT=0
69         
70         *cleanup;
71         proc sql;
72         	drop table ORADB.POC_DUMMY;
 
ORACLE_16: Prepared: on connection 8
SELECT * FROM BILADM.POC_DUMMY
 
 
ORACLE_17: Executed: on connection 8
DROP TABLE  BILADM.POC_DUMMY
 
NOTE: Table ORADB.POC_DUMMY has been dropped.
73         	drop table WORK.DEL_ROW;
NOTE: Table WORK.DEL_ROW has been dropped.
74         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.00 seconds

@Tom This line indicates oracle does indeed return # rows processed to SAS,  doesn't it?

ORACLE: 2 rows inserted/updated/deleted.

[Tom]

I assume that when it pushed the DELETE statement into the remote database to run that SAS did not get back from the remote database any information about how many observations were deleted.  So it did not update SQLOBS.  What happens if you set SQLOBS to some other value before running the DELETE statement?  Does the value stay the same? Or does it get reset to 0?

That message is telling you it had to run the sub-query locally (since it uses a dataset in the WORK library).

Which might explain why the SQLOBS macro variable got updated.  Either it set SQLOBS because PROC SQL ran the SELECT statement in the sub-query and remembered the result.  Or else it somehow ran the whole DELETE locally and so knew how many observations it asked to be deleted and could set SQLOBS.

[Maze]

SQLOBS value gets reset to 0

But as you can see in this log, the line "2 rows inserted/updated/deleted." is issued from the statement that does not include a local dataset and therefore a subquery. Its just a simple delete from ORADB.POC_DUMMY where VAL_COL = 'VALUE1'; SQLOBS is 0, but Logging says 2 rows processed.

55         %LET SQLOBS = -1;
56         *delete, sqlobs will be 0 (but it deletes 2 rows);
57         proc sql feedback;
58         	delete from ORADB.POC_DUMMY
59         	where VAL_COL = 'VALUE1';
 
ORACLE_29: Prepared: on connection 7
SELECT * FROM BILADM.POC_DUMMY
 
NOTE: Statement transforms to:

        select distinct 1
          from ORADB.POC_DUMMY
         where POC_DUMMY.VAL_COL = 'VALUE1';

 
ORACLE_30: Executed: on connection 8
delete from BILADM.POC_DUMMY where BILADM.POC_DUMMY."VAL_COL" = 'VALUE1'
 
ORACLE: 2 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
60         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds
      

61         %PUT NOTE: DEL by VAL &=SQLOBS;
NOTE: DEL by VAL SQLOBS=0

[Tom]

Looks like Oracle said something about number of rows deleted.  But sounds like either SAS did not understand (it is probably just echoing the messages as plain text) or did not care.  You might need to ask SAS technical support. 

Does it really matter?  Are you planning to use it for something?

How reliable is that SQLOBS macro variable count for deletion anyway? 

[Maze]

Yeah that's what it looks like.

I think either is should be reliable oder at least documented that it is unreliable for delete statements.

I am just using it for user feedback on how many rows were deleted/updated.

Workaround would be something like this

proc sql;
	select count(*) 
	into :l_cnt_del
	from ORADB.POC_DUMMY
	where VAL_COL = 'VALUE1';
quit;

But I think it's unfortunate I have to basically do this WHERE operation twice when there should be an automatic variable that does exactly this.

Additional Hint: This also appears to be an issue when you delete by SELECT but both datasets are on the remote DB

50         *delete, sqlobs will be 0 (but it deletes 2 rows);
51         proc sql;
52         	delete from ORADB.POC_DUMMY
53         	where ID_COL in (
54         		select ID_COL from ORADB.DEL_ROW
55         	);
 
ORACLE_145: Prepared: on connection 7
SELECT * FROM BILADM.POC_DUMMY
 
 
ORACLE_146: Prepared: on connection 7
SELECT * FROM BILADM.DEL_ROW
 
 
ORACLE_147: Executed: on connection 8
delete from BILADM.POC_DUMMY where BILADM.POC_DUMMY."ID_COL" in ( select BILADM.DEL_ROW."ID_COL" from BILADM.DEL_ROW )
 
ORACLE: 1 rows inserted/updated/deleted.
ORACLE:  *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
56         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.08 seconds
      cpu time            0.00 seconds
      

57         %PUT NOTE: DEL by VAL &=SQLOBS;
NOTE: DEL by VAL SQLOBS=0

[Tom]

I thought you said that 2 observations were deleted? Why does Oracle message say only 1?

Maybe the unreliable nature of the report from Oracle is why SAS does not use it to populate SQLOBS?

[Maze]

It is a different example - this one uses a subquery for deletion but the queried dataset is also on oracle.
The other one was my proof of concept that used delete by static value.
Different counts because of my demo setup.
The number reported by oracle is correct.

[Maze · Accepted solution]

I found a fix. With proc sql option "noipassthru" the log message and SQLOBS is assigned correctly. So it seems implicit passthrough was indeed the issue.

56         proc sql noipassthru;
57         	delete from ORADB.POC_DUMMY
58         	where VAL_COL = 'VALUE1';
NOTE: 2 rows were deleted from ORADB.POC_DUMMY.

59         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.85 seconds
      cpu time            0.16 seconds
      

60         %PUT NOTE: DEL by VAL &=SQLOBS;
NOTE: DEL by VAL SQLOBS=2