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

[sairamus]

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

[LinusH]

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

[kuridisanjeev]

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

[sairamus]

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

[LinusH · Accepted solution]

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.

[Tom]

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.

[sairamus]

Dear Tom!

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

Thanks,

Max