I have the following code I am using and the output for a field I need as Date Time Formating
Data DATES;
Format CURRENT_DATE
EXP_DATE
REPORT_DATE
date9.;
CURRENT_DATE = today();
EXP_DATE = today()+8;
REPORT_DATE = intnx('Month',CURRENT_DATE,-1,'E');
Call Symput('CURRENT_DATE',CURRENT_DATE);
Call Symput('REPORT_DATE',REPORT_DATE);
Call Symput('EXP_DATE',EXP_DATE);
In my select statement I use :
Select &CURRENT_DATE format=datetime19. as CREATION_DATE_TIME
but instead of todays datetime, I am getting "01JAN1960:06:09:20"
What am I doing incorrectly? if I use format = mmddyy10. I get the right date, just not the proper format I am after.
Use a date format for your new variable, not a datetime format. A datetime format expects a count of seconds from 1960-01-01:00:00:00, while a date is a count of days from 1960-01-01.
Sorry I'm not really following. when I use the below code, the first field comes back with correct date, but the second field using the datetime19. does not.
proc sql;
select &CURRENT_DATE format=mmddyy10. as CREATION_DATE_TIME,
&CURRENT_DATE format= datetime19. as CREATION_DATE_TIME
from BI9146_TMP;
run;
@Jyuen204 wrote:
Sorry I'm not really following. when I use the below code, the first field comes back with correct date, but the second field using the datetime19. does not.
proc sql;
select &CURRENT_DATE format=mmddyy10. as CREATION_DATE_TIME,
&CURRENT_DATE format= datetime19. as CREATION_DATE_TIME
from BI9146_TMP;
run;
This is exactly what @Kurt_Bremser was telling you. If you format a DATE with a DATETIME format, you get nonsensical answers.
@Jyuen204 wrote:
Sorry I'm not really following. when I use the below code, the first field comes back with correct date, but the second field using the datetime19. does not.
proc sql;
select &CURRENT_DATE format=mmddyy10. as CREATION_DATE_TIME,
&CURRENT_DATE format= datetime19. as CREATION_DATE_TIME
from BI9146_TMP;
run;
That is because a DATETIME format is not suited for DATES, as I explained earlier.
you are trying to format a number that is converted as string to date format. Instead store the value in macro variable with date format.
Data DATES;
Format CURRENT_DATE
EXP_DATE
REPORT_DATE
date9.;
CURRENT_DATE = today();
EXP_DATE = today()+8;
REPORT_DATE = intnx('Month',CURRENT_DATE,-1,'E');
Call Symput('CURRENT_DATE',put(CURRENT_DATE,date9.));
Call Symput('REPORT_DATE',put(REPORT_DATE,date9.));
Call Symput('EXP_DATE',put(EXP_DATE,date9.));
A DateTime format cannot be directly applied to a Date value. Date is a count of days, and DateTime is a count of seconds. I believe to convert a Date value to a DateTime value, you'd have to multiply by 24 x 60 x 60 x 60. I think that's right, but check me. Multiplying a Date value by 24 gives the number of days. Multiplying by the first 60 gives the number of hours. Multiplying by the second 60 gives the number of minutes. Multiplying by the third 60 gives the number of seconds.
A simpler approach if you just want the current DateTime value as your creation date might be:
PROC SQL;
CREATE TABLE WORK.New_Table AS
SELECT DATETIME() AS CREATION_DATE_TIME FORMAT=DATETIME19.
FROM WORK.Existing_Table;
QUIT;
Of course you'd need to add additional columns, but the first column would look like the below which I think is what you want, yes?
Jim
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.