BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lethcons
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

5 REPLIES 5
HB
Barite | Level 11 HB
Barite | Level 11

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
SAS Super FREQ

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
Obsidian | Level 7

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

Ksharp
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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