topic How to Insert date and time into fields on adding rows to table in SAS Programming

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

sairamus — Wed, 18 Dec 2013 08:24:03 GMT

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

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

LinusH — Wed, 18 Dec 2013 09:15:23 GMT

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

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

kuridisanjeev — Wed, 18 Dec 2013 09:32:36 GMT

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

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

sairamus — Wed, 18 Dec 2013 10:13:40 GMT

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

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

LinusH — Wed, 18 Dec 2013 12:42:04 GMT

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.

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

Tom — Wed, 18 Dec 2013 17:11:03 GMT

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.

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

sairamus — Thu, 19 Dec 2013 04:41:55 GMT

Dear Tom!

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

Thanks,

Max