Help using Base SAS procedures

DATETIME format while creating a table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

DATETIME format while creating a table

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!


Accepted Solutions
Solution
‎11-02-2016 08:04 AM
Super User
Super User
Posts: 7,407

Re: DATETIME format while creating a table

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;

View solution in original post


All Replies
Solution
‎11-02-2016 08:04 AM
Super User
Super User
Posts: 7,407

Re: DATETIME format while creating a table

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

Re: DATETIME format while creating a table

Thanks a lot, I thought it will be that obvious Smiley Tongue
Super User
Posts: 9,682

Re: DATETIME format while creating a table


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;
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 668 views
  • 1 like
  • 3 in conversation