Inserting date/time in a SQL table in SAS

Question

I am having a little issue inserting the date time into a database table name (TestTable) in SAS EG. The date fields in the TestTable are defined as

Name = EnteredDate  
Type = Date
Length = 8
Format = DATETIME22.3
Informat = DATETIME22.3

Name = LastUpdateDate  
Type = Date
Length = 8
Format = DATETIME22.3
Informat = DATETIME22.3

My existing code is as follows, where i use a select statement to insert as opposed to a values statement. Here is call a PrepTable which I dont use at all, but the select statement allows me to use the datetime() function which works perfectly in inserting the date time without any issues. See below:

INSERT INTO LIBNAME.TestTable (Statuscode, EnteredDate, LastUpdateDate)
Select '2', datetime(), datetime()
From work.PrepTable

I want to do the following:

INSERT INTO LIBNAME.TestTable (Statuscode, EnteredDate, LastUpdateDate)
VALUES ('2', datetime(), date time()) 

This is far more efficient I think since I dont have to query the work.PrepTable. But datetime() will not work in a values statement. I tried the following variations

INSERT INTO LIBNAME.TestTable (Statuscode, EnteredDate, LastUpdateDate)
VALUES ('2', "&sysdate9:00:00:00"dt,"&sysdate9:00:00:00"dt)

This method does not enter the time only the date and the date entered is wrong as well. Another variation I tried is the following after looking around on this site

INSERT INTO LIBNAME.TestTable (Statuscode, EnteredDate, LastUpdateDate)
VALUES ('2', &today_dttm,&today_dttm)

where &today_dttm is sourced from:

%let today_dttm=%sysfunc(dhms(%sysfunc(today()), 0, 0, 0));

This hasn't worked to well either as i keep getting only just the date inserted and not the time. Is there a proper way to enter the current day and time when the insert statement is called without having to use the Select statement and datetime() combo.

I am a newbie to sas and any input would be greatly appreciated. Thanks

Answer 1

There are a few different issues here.

First off, &sysdate9. and &sysdate. are only defined at system startup. To get the current date, you need %sysfunc(today(),date9.) or %sysfunc(datetime(),datetime17.). You could use that in a values statement.

Second, I don't tend to encourage use of the values statement in SAS, because it's pretty minimal in what it supports compared to other SQL flavors. I find it better to insert data using datalines in a SAS dataset and then appending that dataset.

data to_insert;
 input statuscode $;
 entereddate=datetime();
 lastupdatedate=datetime();
datalines;
2
;;;;
run;

proc append base=libname.testtable data=to_insert;
run;

Third, your various attempts to use date variables/functions (&sysdate9, today()) are not including the time because they're date variables/functions and don't include times. Really what my first suggestion is, is simply combining two of your methods, after all. While it is possible to have a decimal-containing date value in SAS which converts to datetime complete with h/m/s, it's not the "norm" unless your data comes from somewhere else (like excel).

Answer 2

Joe's answer covers it, but here is a solution using your SQL.

You almost have it. Try this

%let today_dttm=%sysfunc(datetime());
INSERT INTO LIBNAME.TestTable (Statuscode, EnteredDate, LastUpdateDate)
VALUES ('2', &today_dttm,&today_dttm);

Answer 3

You don't need the %let part, just do this:

INSERT INTO LIBNAME.TestTable (Statuscode, EnteredDate, LastUpdateDate)
VALUES ('2', %sysfunc(datetime()),%sysfunc(datetime()));

Answer 4

These may be useful. They work in WPS, a SAS-compatible system:

%let now=datetime();
%let yesterday=intnx('dtDay',datetime(),-1);
%let today=intnx('dtDay',datetime(),0);
%let tomorrow=intnx('dtDay',datetime(),1);

You can expand on that idea, then use them in your PROC SQL:

PROC SQL;

SELECT &today as today FORMAT=datetime., &tomorrow as tomorrow FORMAT=datetime.
FROM tablename
;

for example.