Hi All,
Request if someone can help me on the below error. I'm simply storing start & end time in a macro variable and passing it into an SQL passthrough to update the start and end times into sql table. What should i change it, so that the table is updated for the start and end times.
ERROR: CLI execute error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic overflow
error converting expression to data type datetime. : [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol
driver][Microsoft SQL Server]The statement has been terminated.
%let transfer_start_time = %sysfunc(datetime());
/*sas code here*/
%let transfer_end_time = %sysfunc(datetime());
proc sql;
connect to sqlsvr (&sqlconn_stg);
execute
( update schema.table
set
StartDate = &transfer_start_time,
EndDate = &transfer_end_time,
RecordCount = 10000,
StatusCode = 'X1'
where
TransferId = 1010 and
StatusCode = 'XP'
) by sqlsvr;
disconnect from sqlsvr;
quit;
Thanks,
Anil
The code in your pass-through block must be valid SQL Srv syntax. The SAS macro variable must therefore resolve to a string which is such valid syntax.
set
StartDate = &transfer_start_time,
EndDate = &transfer_end_time,
What strings do above two macro variable contain - and is it valid SQL Srv syntax?
Hi Patrick,
Below is what im assigning into macro variable.
%let transfer_start_time = %sysfunc(datetime());
/*sas code here*/
%let transfer_end_time = %sysfunc(datetime());
Thanks,
Anil
Check the value of those macro variables. Do they look exactly like what you would hard code in MS SQL server? I highly suspect not.
The key to macro code is make it work first without macro variables and then create the macro portions necessary.
Code like: %let transfer_start_time = %sysfunc(datetime()); populates SAS macro variable &transfer_start_time with a SAS DateTime value. Right now when I run it this value would be: 1799530675.664
Each database deals with Dates, DateTimes and TimeStamps differently. You can't just pass in a SAS DateTime value in a pass-through SQL block (which is in data base native SQL) as then the database doesn't have any information how to convert this SAS value to the database specific representation.
You need either to look up how to pass in a STRING - and populate the SAS Macro variable with such a string - or if you just want to pass in the system time equivalent of what you would get with the SAS Datetime() function then use the equivalant database function in your pass-through SQL block. For a SQL Server DateTime column this appears to be function SYSDATETIME() https://msdn.microsoft.com/en-us/library/bb630353.aspx
I haven't worked a lot with SQL Server so I don't know if function SYSDATETIME() will resolve for every single row or only once. I'm sure SQL Server has implemented options for both cases and it's just a question of RTM to figure out the right syntax.
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.