Using Enterprise Guide.
When I run the attached code, I get the following (relevant snips) in the log:
24 create table test (valid_from datetime format=datetime20.);
________
1
WARNING 1-322: Assuming the symbol DATE was misspelled as datetime.
create table WORK.TEST( bufsize=8192 )
(
valid_from num format=DATETIME20. informat=DATE.
);
30 quit;
How do I create datetime columns?
proc sql;
create table test (valid_from datetime format=datetime20.);
quit;
proc sql;
describe table test;
quit;
Just specify "num" instead of datetime. Datetime values are just numeric fields, so as long as you're populating the tables with valid SAS datetime values you should be fine.
Just specify "num" instead of datetime. Datetime values are just numeric fields, so as long as you're populating the tables with valid SAS datetime values you should be fine.
e.g.
proc sql;
create table test (valid_from num format=datetime20.);
quit;
proc sql;
describe table test;
insert into test
values('01JUN2015:00:12:00'dt);
quit;
Thank you, I thought the logical thing would be to declare the type like one does with date columns, I was wrong.
Dates are numbers too, not a type either.
There is no datetime type in SAS, only numbers and characters. Change it to NUM and it works like a charm.
Datetime is the number of seconds from Jan 1, 1960.
proc sql;
create table test (valid_from num format=datetime20.);
quit;
proc sql;
describe table test;
quit;
A datetime value is stored in a numeric variable. Just use any of the aliases that PROC SQL accepts for a number.
Syntax error, expecting one of the following: CHAR, CHARACTER, DATE, DEC, DECIMAL, FLOAT, INT , INTEGER, NUM, NUMERIC, REAL, SMALLINT, VARCHAR.
So anything except CHAR, CHARACTER or VARCHAR will work.
proc sql;
create table test (valid_from real format=datetime20.);
describe table test;
quit;
Or you could just create the table using a data step, no need to bother with SQL.
data test ;
attrib valid_from format=datetime20.;
stop;
run;
proc contents data=test;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.