DATETIME format while creating a table

[pkonopnicki]

Hi guys,

I'd like to create a table with a datetime column and then insert some random dates.

For example:

proc sql;
create table PALETA
(DATA_ID DATETIME);
insert into paleta (DATA_ID) values ('1Sep2015:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Oct2015:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Nov2015:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Dec2015:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Jan2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Feb2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Mar2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Apr2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1May2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Jun2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Jul2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Aug2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Sep2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Oct2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Nov2016:0:0:0'dt);
insert into paleta (DATA_ID) values ('1Dec2016:0:0:0'dt);
quit;

The issue is, I receive asterisk result instead of a datetime.

In order to make it work, I need to create another procedure:

proc sql;
create table Paleta2 as
SELECT DATA_ID FORMAT=DATETIME20.
from paleta;
quit;

Can you please help out how to make it in one procedure instead of adding the second one?

thanks!

[RW9 · Accepted solution]

I assume this is not using a database, hence just adding format statement should do the trick:

proc sql;
  create table PALETA (DATA_ID DATETIME format=datetime20.);
  insert into paleta (DATA_ID) values ('1Sep2015:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Oct2015:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Nov2015:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Dec2015:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Jan2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Feb2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Mar2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Apr2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1May2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Jun2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Jul2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Aug2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Sep2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Oct2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Nov2016:0:0:0'dt);
  insert into paleta (DATA_ID) values ('1Dec2016:0:0:0'dt);
quit;

[pkonopnicki]

Thanks a lot, I thought it will be that obvious 😛

[Ksharp]

proc sql;
create table PALETA
(DATA_ID NUM format=datetime.);
insert into paleta (DATA_ID) values ('1Sep2015:0:0:0'dt);

select * from paleta;
quit;