topic Re: Use of datetime() function in SQL in SAS Programming

Use of datetime() function in SQL

lethcons — Thu, 22 Feb 2018 16:52:06 GMT

Consider this:

proc sql;
insert into mysastab(key, datetime)
select key,
       datetime()
from mysastab0
;
quit;

I think I may have heard that the datetime() function gets evaluated once and then that constant value is used for each row inserted into mysastab from mysastab0. I want to avoid the situation where the value for datetime is evaluated for every input row and so may not show exactly the same time for each row inserted.

Which is it? Can anyone give an authoritative answer?

Thanks.

Re: Use of datetime() function in SQL

HB — Thu, 22 Feb 2018 19:05:28 GMT

If I do this:

data my_sas_table;
   input key mydatetime datetime.;
datalines;
1 01jan2018:16:24:43.43
2 01jan2018:16:24:43.43
3 01jan2018:16:24:43.43
4 01jan2018:16:24:43.43
5 01jan2018:16:24:43.43
6 01jan2018:16:24:43.43
7 01jan2018:16:24:43.43
;
run;

proc sql;
create table two_my_sas_table (key numeric, mydatetime date);
insert into two_my_sas_table (key, mydatetime)
select key, datetime()
from my_sas_table;
quit;

proc print noobs;
format mydatetime datetime.;
run;

I get

    The SAS System      16:16 Tuesday, February 20, 2018  27

                                     key       mydatetime

                                      1     22FEB18:12:57:24
                                      2     22FEB18:12:57:24
                                      3     22FEB18:12:57:24
                                      4     22FEB18:12:57:24
                                      5     22FEB18:12:57:24
                                      6     22FEB18:12:57:24
                                      7     22FEB18:12:57:24

suggesting that the inserted times are the same.

Re: Use of datetime() function in SQL

BrunoMueller — Thu, 22 Feb 2018 20:25:28 GMT

Proc SQL has the NOCONSTDATETIME option, this will control, whether the today() or datetime() function are evaluated once or every time. To be on the save side, I suggest to use a macro expression like

%sysfunc( datetime() )

this will be evaluated only once.

Re: Use of datetime() function in SQL

Ksharp — Fri, 23 Feb 2018 04:46:52 GMT

OR use system macro variable.

data long;
  do i = 1 to 10;
    output;
  end;
run;

proc sql noconstdatetime;
  drop table two_my_sas_table;
  create table two_my_sas_table (
    key numeric
    , mydatetime numeric format=datetime23.3
    , mydatetime2 numeric format=datetime23.3
    );
  insert into two_my_sas_table 
    select i, datetime(), dhms("&sysdate"d,0,0,"&systime"t)
      from long
  ;
quit;

proc print noobs;run;

Re: Use of datetime() function in SQL

Patrick — Fri, 23 Feb 2018 09:20:18 GMT

@lethcons

The SQL FEEDBACK option will tell you what's fact. Default is a constant value.

40         proc sql feedback;
41           insert into mysastab(key, datetime)
42             select key,
43               datetime()
44             from mysastab0
45           ;
NOTE: Statement transforms to:

        insert
          into WORK.MYSASTAB(MYSASTAB.key, MYSASTAB.datetime)
        select MYSASTAB0.key, '  23FEB2018:20:16:12'DT
          from WORK.MYSASTAB0;

Re: Use of datetime() function in SQL

lethcons — Mon, 26 Feb 2018 12:22:40 GMT

Thanks to all who replied. It was the CONSTDATETIME option I was looking for.