Manipulating Data with the SQL Pass-Through Facility (delete obs)?

[kjohnsonm]

Hello all,

I have a passthrough question on deleting a few obs directly in MS SQL?  (base SAS, Windows10 SAS9.4M4 )

my code:

proc sql;
connect to odbc(dsn=My_dsn user=my_user password=my_pass);
select * from connection to odbc
   (
                        select strm, snap_date, emplid
						FROM [my_db].[dbo].[my_large_table] a
						where exists ( select 1 from [my_db].[dbo].[my_small_del_table] b
							where a.strm = b.strm and a.emplid = b.emplid and a.snap_date = b.snap_date)
	);

execute (delete a.* FROM [my_db].[dbo].[my_large_table] a
						where exists ( select 1 from [my_db].[dbo].[my_small_del_table] b
							where a.strm = b.strm and a.emplid = b.emplid and a.snap_date = b.snap_date)) by odbc;
disconnect from odbc;
quit;

The first part of this code (the select) returns data just fine from my MS SQL db instance and the exact obs I want to delete.  it returns data like this to my SAS session:

strm snap_date emplid
2177 25AUG2017:00:00:00.000 021595273
2177 01SEP2017:00:00:00.000 019595272
2177 25AUG2017:00:00:00.000 011799517
2177 01SEP2017:00:00:00.000 011569510

However the second part (the execute) gives me this error:
ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '*'.
1561 disconnect from odbc;
1562 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds

I have tried these variations:
delete a.* FROM

delete FROM

delete * FROM

in place of the execute line start.  Is this possible to delete using sas pass through this way, and if yes. Can someone help me fix my code?

The larger data set is in the many millions and the small data set that needs to be deleted is just 104 obs. I really do not want to use the processing time to read to sas the full X million obs to drop the 104 obs then wright the full data set back to the db.  and I will be doing this weekly so doing it manually on the SQL server is not ideal when the rest of my SAS code runs unattended by a schedule task.  TIA  -KJ

PS I have been trying to follow the examples show on page:
http://support.sas.com/documentation/cdl/en/spdsug/67358/HTML/default/viewer.htm#p0dc2sn22lw68xn0zu8...
"Accessing and Manipulating Data with the SQL Pass-Through Facility"

If there is a better doc for sas 9.4 I will gladly use it but have not seen it so far...

[utrocketeng]

perhaps something like this:

delete a.* 
FROM 
	[my_db].[dbo].[my_large_table] a
	INNER JOIN [my_db].[dbo].[my_small_del_table] b ON a.strm = b.strm and a.emplid = b.emplid and a.snap_date = b.snap_date
WHERE b.strm is not null

i would suggest testing in a client tool (i use SQL Server a lot so i use SSMS) before trying to run it through SAS.  often the use of a client tool provides better error messaging.

good luck

[kjohnsonm]

-engineer,
I tried to check this code directly on SSMS, it will allow the select but blows up on the delete.  when I pre-tested it I stopped on the 'select'. Sorry my unforced error.  so I have been told this will work with oracle but not on a MS SQL server. I their for tried this coding idea.

proc sql;
/* ...  I left out the user/pass lines this time etc */

/* So just upload the small data set to sql server and work them against each other locally, took about 2 min to process max */
/* Assumes you have already set small temp data set: my_small_table on to sql server */

/*	Count original data set size */
select * from connection to odbc
	(				select count(*) as all_count from my_original_table);

/*	Make temp data set without the small set of records using left join, this is a temp data set */
execute (SELECT a.*
			INTO [my_data_base].[dbo].[my_original_table_temp] 
			FROM [my_data_base].[dbo].[my_original_table] a
			left join [my_data_base].[dbo].[my_small_table] b
			on a.strm = b.strm and a.emplid = b.emplid and a.snap_date = b.snap_date
			where b.strm is null and b.emplid is null and b.snap_date is null; ) by odbc;

/*			count new total obs in temp data set */
select * from connection to odbc
	(				select count(*) as tempcount from my_original_table_temp);

/*	Delete the original data set records */
execute (DELETE FROM [my_data_base].[dbo].[my_original_table];) by odbc;

/*	Put the temp data set just made on SQL server into correct data set table */
execute (INSERT INTO [my_data_base].[dbo].[my_original_table]
SELECT * FROM [my_data_base].[dbo].[my_original_table_temp]; ) by odbc;

/* PS I recommend if you need to use this over and over to clean up your temp file by dropping them here, but did not show it... */

disconnect from odbc;
quit;

it's working for me.  just more steps... but very fast compared to reading and writing millions of rows for such a small change.