How to Insert date and time into fields on adding rows to table

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How to Insert date and time into fields on adding rows to table

Hi everybody!

I have a question about insertng date and time into table fields.

Here is my table:

CUSTOMER_NUMBER

ACCOUNT_NUMBER

CARD_NUMBER

CAMPAIGN_ID

WAVE_ID

DATE_INSERTED

TIME_INSERTED

SYNC

SOEID

DATE_MODIFIED

I need to insert sysdate into DATE_INSERTED, DATE_MODIFIED and systime into TIME_INSERTED fields on adding rows into table.

Thank you very much for help!

Have a nice day!

Max


Accepted Solutions
Solution
‎12-18-2013 07:42 AM
Super User
Posts: 5,424

Re: How to Insert date and time into fields on adding rows to table

This clears things up (just a bit).

Do you look for some kind of automatic generation of columns values to be assigned during inserts?

This is a functionality in many RDBMS, and often called default constraints.

To my knowledge, nothing similar is not implemented in SAS.

What you could do is create a macro which the users use when they wish to insert data, and then you'll have the date/time columns assigned by that macro.

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 5,424

Re: How to Insert date and time into fields on adding rows to table

How do insert values for the other columns?

Independent of update/insert technique, just use the macro variables in assignment statements:

date_inserted = "&SYSDATE"D;

or SQL:

"&SYSDATE"D as date_inserted

Data never sleeps
Super Contributor
Posts: 276

Re: How to Insert date and time into fields on adding rows to table

Are you asking about inserting data into mentioned variable ? or your quiestion about how to get Sys date and time in SAS..

to get  system date and time ,you can use Automaic macro  variables like &SYSDATE and &SYSTIME.

If you want to insert the same into the fields ,use like Date_inserted = "&SYSDATE"D.

Thanks,

Sanjeev.K

New Contributor
Posts: 3

Re: How to Insert date and time into fields on adding rows to table

Thanks a lot for writing me!

So, this is how I'm creating table:

proc sql;

create table table_name
(CUSTOMER_NUMBER varchar(20) not null,
ACCOUNT_NUMBER varchar(35),
CARD_NUMBER varchar(19),
CAMPAIGN_ID varchar(20) not null,
WAVE_ID varchar(20) not null,
DATE_INSERTED num format = date9.,
TIME_INSERTED num format = time.,
SYNC varchar(2),
SOEID varchar(2),
DATE_MODIFIED num format = date9.);

This is how I was trying to write a rule for inserting date:

alter table table_name modify DATE_INSERTED,DATE_MODIFIED ON UPDATE today() NOT NULL ;

Please help me on how to write full syntax for inserting sysdate (to DATE_INSERTED & DATE_MODIFIED) and systime (to TIME_INSERTED)

After creation users will upload data using - insert select

Thanks a lot

Max

Solution
‎12-18-2013 07:42 AM
Super User
Posts: 5,424

Re: How to Insert date and time into fields on adding rows to table

This clears things up (just a bit).

Do you look for some kind of automatic generation of columns values to be assigned during inserts?

This is a functionality in many RDBMS, and often called default constraints.

To my knowledge, nothing similar is not implemented in SAS.

What you could do is create a macro which the users use when they wish to insert data, and then you'll have the date/time columns assigned by that macro.

Data never sleeps
Super User
Super User
Posts: 7,039

Re: How to Insert date and time into fields on adding rows to table

Use the date(), datetime() or time() functions to get the actual time that the data was inserted.  If you use the automatic macro variables &SYSDATE9 and &SYSTIME you will instead get the date and time when the SAS session started.

New Contributor
Posts: 3

Re: How to Insert date and time into fields on adding rows to table

Dear Tom!

Thanks for clarification about macro, it's realy usefull infomation.

Thanks,

Max

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 2681 views
  • 10 likes
  • 4 in conversation