Formating Date Time

[Jyuen204]

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.

[Kurt_Bremser]

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.

[Jyuen204]

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;

[PaigeMiller]

---

@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.

[Kurt_Bremser]

---

@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.

[smantha]

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.));

[jimbarbour]

@Jyuen204,

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