BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sairamus
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

6 REPLIES 6
LinusH
Tourmaline | Level 20

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
kuridisanjeev
Quartz | Level 8

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
Calcite | Level 5

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
Tourmaline | Level 20

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
Tom
Super User Tom
Super User

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
Calcite | Level 5

Dear Tom!

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

Thanks,

Max

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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