Use of datetime() function in SQL

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Use of datetime() function in SQL

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.

 


Accepted Solutions
Solution
‎02-26-2018 07:21 AM
SAS Super FREQ
Posts: 817

Re: Use of datetime() function in SQL

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;

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 266

Re: Use of datetime() function in SQL

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. 

Solution
‎02-26-2018 07:21 AM
SAS Super FREQ
Posts: 817

Re: Use of datetime() function in SQL

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;
Contributor
Posts: 20

Re: Use of datetime() function in SQL

Posted in reply to Bruno_SAS

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

Super User
Posts: 10,766

Re: Use of datetime() function in SQL

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;
Respected Advisor
Posts: 4,736

Re: Use of datetime() function in SQL

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 193 views
  • 2 likes
  • 5 in conversation