SAS pass through and MS SQL CTE?

[kjohnsonm]

Hello,
I can do many types of SAS 9.4 pass through to an MS SQL server, (I have update rights on).
I can write CTE's directly on the MS SQL SMS tool that work nicely.   
I was wondering if anyone has any tips for me on what might be wrong here?   Sorry I know this is a cross over questions, and no I do not have the normal: have data & want data, code and examples.

/* This simple passthrough works just fine save I have hidden my db/username/password */

proc sql;
connect to odbc  ( datasrc='[my_db_here]' user='[my_user_here]' password='[my_pass_here]');;
select * from connection to odbc
	(	
	BEGIN TRY  
	BEGIN TRAN  
	select top 5 * from [my_db_here].[dbo].[hms_source_table_status]
	COMMIT TRAN  
END TRY  
BEGIN CATCH  
       ROLLBACK TRAN  
END CATCH
;
);quit;

/* I can also create a table out of this kind of query just fine,   What seems to escape me for the moment is a passthrough CTE? */

proc sql;
connect to odbc  ( datasrc='my_db' user='my_user' password='my_password');
select * from connection to odbc
	(	
	BEGIN TRY  
	BEGIN TRAN  
	with my_max_date as (select distinct
cast([source_date] as date) as source_date
      ,max([source_count]) as source_count
      ,[table_processed]
FROM [my_db].[dbo].[hms_source_table_status]
group by cast([source_date] as date), [table_processed]
)select * from my_max_date
order by [source_date] desc, [table_processed]
	COMMIT TRAN  
END TRY  
BEGIN CATCH  
       ROLLBACK TRAN  
END CATCH
;
);quit;

/* in this CTE case I do realize the data will just print, not save to a data set name, but am just taking baby steps. 

I truly do want the group by cast feature I have added here  */

I can share my error:

ERROR: CLI prepare error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near
the keyword 'as'.
SQL statement: BEGIN TRY BEGIN TRAN with my_max_date as (select distinct cast([source_date] as date)
as source_date ,max([source_count]) as source_count ,[table_processed] FROM
[admissions].[dbo].[hms_source_table_status] group by cast([source_date] as date),
[table_processed] )select * from my_max_date order by source_date desc, [table_processed]
COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH ;.
97 ! quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds


---TIA Keith

[kjohnsonm]

PS if anyone does not know what a CTE is, this is a quick link:
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sq...

PSS
and the CTE code:
with my_max_date as (select distinct
cast([source_date] as date) as source_date
,max([source_count]) as source_count
,[table_processed]
FROM [my_db].[dbo].[hms_source_table_status]
group by cast([source_date] as date), [table_processed]
)select * from my_max_date
order by [source_date] desc, [table_processed]

works just fine on my server with the one db edited back to my local name.

[SASKiwi]

Does the CTE code produce a result set? Since I don't see a SELECT on the front I'm not clear about this. If there is no result set then you need to use the EXECUTE statement in SQL Passthru, not a SELECT. An EXECUTE would look like this:

proc sql;
connect to odbc  ( datasrc='my_db' user='my_user' password='my_password');
execute
	(	
	BEGIN TRY  
	BEGIN TRAN  
	with my_max_date as (select distinct
cast([source_date] as date) as source_date
      ,max([source_count]) as source_count
      ,[table_processed]
FROM [my_db].[dbo].[hms_source_table_status]
group by cast([source_date] as date), [table_processed]
)select * from my_max_date
order by [source_date] desc, [table_processed]
	COMMIT TRAN  
END TRY  
BEGIN CATCH  
       ROLLBACK TRAN  
END CATCH
;
) by odbc;
quit;

[kjohnsonm]

sorry did not see your full question when I replied... reading again...   [edited]

[SASKiwi · Accepted solution]

OK, but the error message you posted was a syntax error from SQL Server and nothing to do with SAS. Try pasting that exact Pass thru code back into SSMS and see if it works.

[kjohnsonm]

You are right about where the error lives.
The issue is the roll back part of the code.  This with my db/password info put back in works just fine

proc sql;
connect to odbc ( datasrc='[my_db_here]' user='[my_user_here]' password='[my_pass_here]');
select * from connection to odbc
(

with my_max_date as (select distinct
cast([source_date] as date) as source_date
,max([source_count]) as source_count
,[table_processed]
FROM [my_db].[dbo].[hms_source_table_status]
group by cast([source_date] as date), [table_processed]
)select * from my_max_date
order by source_date desc, [table_processed]
;
);quit;

...unfortunately for me I wanted that to work too so I could integrate one more level and add some delete/update MS SQL queries to this code.  This was just a step along the way I was stuck on.
I can do updates deletes with pass through in general just not with a CTE and them and the rollback feature.

[Tom]

Did you try using EXECUTE() instead of CONNECTION TO () to run that strange BEGIN stuff?

You actually want to retrieve something you might have to use two steps. One to EXECUTE() and perhaps store into a table in the database and then one to actually transfer the data to the SAS server.

[kjohnsonm]

Sorry about delay; Summer vacation...
Same error with the begin try/begin tran etc. when I take those trapping lines out your code also works.

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near
the keyword 'as'.
139 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds



note:

the first part of the code starts out with the line :
with my_max_date as (

that sapid of code is basically defining a temp table that does not exist in the SQL server whatever is in between the next two parentheses pair is the virtual table data and the next reference to the table name:
my_max_date is the select you are looking for or that is my interpretation anyway.

[SASKiwi]

You may need to tweak your ODBC connection settings to get things working. Try CONNECTION=GLOBAL plus AUTOCOMMIT = NO and splitting your CTE code and the following SELECT into separate queries, the first in a SAS EXECUTE, the second in a SAS SELECT following this:

connect to odbc ( datasrc='[my_db_here]' user='[my_user_here]' password='[my_pass_here]') connection=global autocommit = no;

[kjohnsonm]

let me look into this. I have not seen this option before. thanks. -KJ

[kjohnsonm]

proc sql;
connect to odbc  ( datasrc='[my_db]' user='[my_user]' password='[my_password]');
/*create table temp1 as */
select * from connection to odbc
	(	
	BEGIN TRANSACTION
	BEGIN TRY
		with my_max_date as (select distinct
	cast([source_date] as date) as source_date
		  ,max([source_count]) as source_count
		  ,[table_processed]
	FROM [my_db].[dbo].[hms_source_table_status]
	group by cast([source_date] as date), [table_processed]
	)select * from my_max_date
	order by [source_date] desc, [table_processed]
	
	COMMIT TRANSACTION 
END TRY
BEGIN CATCH  
IF @@TRANCOUNT <> 0
BEGIN
       ROLLBACK TRANSACTION
END
/*  -- Execute error retrieval routine.  */
	SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
;
);quit;

... I totally got this to work as I was hoping to, thanks to everyone that gave me pointers. I can replace my simple CTE with a insert/delete CTE combo now and if either fails it will roll back.   
Have a great SAS day -KJ

PS if I typo a field name etc. inside the SMS code it will total tell me what I am doing wrong.  I love it.

[SASKiwi]

@kjohnsonm  - I'm loving it too. We do similar stuff in MS SQL Server too. If you wanted to make your code more generic, you could put it into a SAS macro and feed it an SQL Server table name as a parameter if you just wanted to check a single table, or select what tables were processed in a particular date range.

[kjohnsonm]

I do that all the time, nice.

If you know how to read the metadata per server you connect to you can really get creative with your code and data extraction... have a great day.

/*'full_list' is a SAS data set with fields r_db, and new_table as columns, this process calls the macro 'read_test' over till it has no more obs.

/* This data step will walk the list of the 'full_list' data set just created and process once per obs found or skip over if null */
/* It just updates the obs_ct info or need_sql_tb fields for later use. */
/*data temp;*/
data _NULL_;
set full_list;
code = catt('%nrstr(',
'%read_test(',
r_db,
',',
new_table,
')',
');');
call execute(code);
run;quit;