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
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.
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;
sorry did not see your full question when I replied... reading again... [edited]
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.
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.
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.
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.
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;
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.
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.