topic Re: Current timestamp equivalent function in SAS via Implicit passthrough in SAS Programming

Current timestamp equivalent function in SAS via Implicit passthrough

David_Billa — Sat, 18 Dec 2021 06:53:20 GMT

I want to convert the following SQL query to proc SQL implicit pass through. But I'm not certain how to identify the equivalent current_timestamp function in SAS. When I tried with SAS datetime() function, it is yielding the value with numbers. Should I need to use to Format (e.g. Datetime26.7) to display the timestamp value? If yes, may I know where should I use the Format in Proc SQL implicit passthrough code?

SQL query which works in SQL server:

update Employee set STAT_DESC='FINISHED', END_TMST=current_timestamp where act_ind='Y'

SAS code:

proc sql; 
update sql_lib.Employee set OBJ_STAT_DESC="FINISHED", END_TMST=<looking for valid function and format> where act_ind="Y";
quit;

Re: Current timestamp equivalent function in SAS via Implicit passthrough

SASKiwi — Sat, 18 Dec 2021 07:03:42 GMT

Why do you want to switch to implicit passthru? If you stick to explicit passthru you don't need to change anything:

proc sql;
  connect to SQLSRVR using SQL_LIB;
  execute (update Employee set STAT_DESC='FINISHED', END_TMST=current_timestamp where act_ind='Y') by SQLSRVR;
quit;

Re: Current timestamp equivalent function in SAS via Implicit passthrough

ballardw — Sat, 18 Dec 2021 07:06:22 GMT

To help someone with more of a clue about this than I do you might make sure that the specific database and connection method are mentioned. I believe that the ODBC vs SAS/Access for XXX may have different restrictions even though talking to the same data base. The connection may also make a difference on how dates/times or datetime values need to pass.

Re: Current timestamp equivalent function in SAS via Implicit passthrough

David_Billa — Sat, 18 Dec 2021 07:16:31 GMT

@SASKiwi @ballardw I want to switch to implicit pass through as in the later stage we will use SAS macro variable in the where clause and expand the filter. May I request you to provide me the solution using implicit pass through?

Re: Current timestamp equivalent function in SAS via Implicit passthrough

Kurt_Bremser — Sat, 18 Dec 2021 07:43:49 GMT

First, check how the timestamp has to be supplied as a literal in the target database. Then build that literal in SAS for use in the pass-through.

Re: Current timestamp equivalent function in SAS via Implicit passthrough

ballardw — Sat, 18 Dec 2021 07:48:42 GMT

@David_Billa wrote:

@SASKiwi @ballardw I want to switch to implicit pass through as in the later stage we will use SAS macro variable in the where clause and expand the filter. May I request you to provide me the solution using implicit pass through?

I can't really help on this. No access to, or experience with, any of the DB connections.

Re: Current timestamp equivalent function in SAS via Implicit passthrough

David_Billa — Sat, 18 Dec 2021 07:58:59 GMT

Could you please point me to one simple example?

Re: Current timestamp equivalent function in SAS via Implicit passthrough

SASKiwi — Sat, 18 Dec 2021 08:10:16 GMT

I'm sorry but since you haven't posted your complete requirement, I've given you the best solution for the part you did post. If you post your complete requirement then we will have a better chance of providing the best solution.

Re: Current timestamp equivalent function in SAS via Implicit passthrough

David_Billa — Sat, 18 Dec 2021 08:59:55 GMT

Can we use the SAS macro variable after the execute statement?

E.g. execute (update.....where act_ind='&y.')

Re: Current timestamp equivalent function in SAS via Implicit passthrough

Kurt_Bremser — Sat, 18 Dec 2021 09:55:38 GMT

You can use macro variables in every piece of code that SAS handles, but you must use them correctly.

Since macro variables are not resolved within single quotes, you must make the single quotes part of the macro variable when you create it, and then call it without any quotes.

Re: Current timestamp equivalent function in SAS via Implicit passthrough

SASKiwi — Sat, 18 Dec 2021 22:07:05 GMT

@David_Billa - Yes of course as long as you use double quotes to resolve the macro variable as explained by @Kurt_Bremser

execute (update.....where act_ind="&y.")

If the database you are using objects to the double quotes then use this instead:

execute (update.....where act_ind= %str(%')&y.%str(%'))

Re: Current timestamp equivalent function in SAS via Implicit passthrough

Patrick — Sun, 19 Dec 2021 00:04:41 GMT

@David_Billa

SAS macro variables will resolve BEFORE the SQL executes - and this applies for implicit and explicit SQL. For this reason you can also use a SAS macro variable within explicit SQL - you just need to populate it with a value appropriate the the explicit SQL syntax.

The first thing you have to figure out is how the string you pass to SQL Server needs to look like so it gets interpreted as a datetime value.

I don't have access to a SQL server right now but Google tells me that something like the following should work:
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09.555');

If that's true then all you need to do is populate a macro variable with: 'yyyy-mm-dd hh:mm:ss.fff'

Below sample doing this using datetime() - but you could of course use any other SAS datetime value (=value that is the count of seconds since 1Jan1960).

%let ts_string=%sysfunc(datetime(),E8601DT23.3);
%let ts_string=%sysfunc(translate(&ts_string,' ','T'));
%let ts_string=%unquote(%nrbquote('&ts_string'));
%put &=ts_string;

And now you could use this string in your pass-through SQL

update Employee set STAT_DESC='FINISHED', END_TMST=convert(datetime, &ts_string) where act_ind='Y'

Re: Current timestamp equivalent function in SAS via Implicit passthrough

Tom — Sun, 19 Dec 2021 01:11:30 GMT

What is the source of the DATETIME value? Do you want it from the system clock? On which system? Your SAS system? Or the database server? If the later then find out what syntax you need to use in that database's version of SQL to use the system clock.

If you are going to reference a variable like in your example then they should not be any change to the code.

But if you want to use a literal datetime value that you have calculated in SAS then it depends on what the remote database wants as the style for specifying datetime values. I quick google for SAS SQL SERVER found:

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15

The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.

So use the E8601DT format to generate the datetime literal. Make sure to include the single quotes in the value.

data _null_;
  call symputx('CURRENT_TIMESTAMP',quote(put(datetime(),e8601dt.),"'"));
run;
%put &=CURRENT_TIMESTAMP;

proc sql;
connect using sqllib;
execute by sqllib
(update Employee
  set STAT_DESC='FINISHED'
    , END_TMST=&current_timestamp 
  where act_ind='Y'
);
quit;

Re: Current timestamp equivalent function in SAS via Implicit passthrough

David_Billa — Sun, 19 Dec 2021 05:08:42 GMT

Thank you. How to use this approach using Libname statement (Implicit Pass through)?

Re: Current timestamp equivalent function in SAS via Implicit passthrough

Tom — Sun, 19 Dec 2021 19:45:48 GMT

@David_Billa wrote:
Thank you. How to use this approach using Libname statement (Implicit Pass through)?

With SAS code you can use the DATETIME() function directly.

proc sql; 
update sql_lib.Employee
  set OBJ_STAT_DESC="FINISHED"
     , END_TMST=datettime() 
  where act_ind="Y"
;
quit;

Or the actual number of seconds.

%let current_timestamp=%sysfunc(datetime());
proc sql; 
update sql_lib.Employee
  set OBJ_STAT_DESC="FINISHED"
     , END_TMST=&current_timestamp
  where act_ind="Y"
;
quit;

Or if you want to make a datetime literal use the DATETIME format to convert the number of seconds into a string that the DATETIME informat can read enclosed in quotes with DT after it.

%let current_timestamp="%sysfunc(datetime(),datetime19.)"dt;
proc sql; 
update sql_lib.Employee
  set OBJ_STAT_DESC="FINISHED"
     , END_TMST=&current_timestamp
  where act_ind="Y"
;
quit;