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
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.
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
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
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
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.
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.
Dear Tom!
Thanks for clarification about macro, it's realy usefull infomation.
Thanks,
Max
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.