Use of datetime() function in SQL

[lethcons]

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.

[HB]

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. 

[BrunoMueller · Accepted solution]

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.

See also this code sample

data long;
  do i = 1 to 1e5;
    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(), %sysfunc( datetime() )
      from long
  ;
quit;

proc freq data=work.two_my_sas_table;
  table mydatetime mydatetime2;
run;

[lethcons]

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

[Ksharp]

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;

[Patrick]

@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;