What's the best approach to store datetime in macro variable?

[WorkingMan]

My ETL script will first have to load from a control table whereby it stores the last batch date time. What I want is to assign the last batch date time (eg. 01Jan2020:00:00:00) for other scripts within the same SAS Session (SAS DI Studio) to extract data according to batch date time from the source database.

Currently, i use:

proc sql noprint;

select max(batchdttm) into:datetime

from controltbl;

quit;

But, when i %put batchdttm, it shows 1-E909(like excel numeric format).

I understand that SAS Datetime is made up of numbers. For this, what is the best approach?

Do I need to keep the batchdttm data from controltbl table as number format before assigning to macro variable? 

If yes, in my other ETL Scripts, I need to convert it to datetime using some kind of transformation/function to the actual datetime to do filter when doing extraction from the source database?

Or it doesnt matter if it is formatted as datetime or not as assigning to macro will ignore the format and only pass numbers to it?

[PaigeMiller]

select max(batchdttm) format=16.0 into :datetime from controltbl;

In general, macro variables containing SAS date values and SAS date/time values are best left as numbers. Formatting the macro variables to appear human readable (for example 13MAY2020:00:00:00) for the purposes of mathematical or logical comparisons is not necessary, and doesn't work unless you go through the extra work of unformatting them so the mathematical or logical operation can happen. The only time you want macro variables to be human readable is if they are going to be used in titles or labels.

[WorkingMan]

Really appreciate for your explanation and the format in a proc sql statement.

If i want to extract data in another scripts based on the date/time in &datetime, how do i convert them into datetime format?

I tried the following script:

proc sql;
create table a as
select *
from CUSTOMER
WHERE PROCESS_DT = "&batchdttm"dt;
quit;

It prompts error of

ERROR: Invalid date/time/datetime constant "188087323"dt

Do take note that PROCESS_DT is a datetime column. 

[PaigeMiller · Accepted solution]

---

@WorkingMan wrote:

Really appreciate for your explanation and the format in a proc sql statement.

If i want to extract data in another scripts based on the date/time in &datetime, how do i convert them into datetime format?

I tried the following script:

proc sql;
create table a as
select *
from CUSTOMER
WHERE PROCESS_DT = "&batchdttm"dt;
quit;

It prompts error of

ERROR: Invalid date/time/datetime constant "&batchdttm"dt

Do take note that PROCESS_DT is a datetime column. 

---

You don't format the values of macro variable for mathematical or logical operations. Therefore, it is a number which is the SAS date or date/time value, and it works without formatting. So you don't need to surround the macro variable with double-quotes and follow it with dt (or d if it is a date variable)

WHERE PROCESS_DT = &batchdttm;

s 

[RichardDeVen]

A SAS data time value is simply the number of second from an epoch (0-datetime). 

Select the value with an associated format such as 14.2, instead not having one and the default (best8.?) is used to hold the value representation.

Example:

data have;
  dt = datetime();
run;

proc sql noprint;
  select max(dt) format=14.2 into :MAXDT from have;

%put &=MAXDT;
---------------- LOG ----------------
MAXDT= 1905063318.97

Sometimes you need to store the exact numeric value in a macro variable, instead of a possibly inexact formatted representation of the value.  In that rarer case, the value format could be HEX16. , but utilizing the macro variable requires more coding.

Example:

proc sql noprint;
  select max(dt) format=HEX16. into :MAXDT_RAW from have;

%put NOTE: MAXDT_RAW                       &MAXDT_RAW;
%put NOTE: MAXDT_RAW parsed:               %sysfunc(INPUTN(%superq(MAXDT_RAW),HEX16.));
%put NOTE: MAXDT_RAW parsed and formatted: %sysfunc(INPUTN(%superq(MAXDT_RAW),HEX16.), DATETIME20.);
---------------- LOG ----------------
NOTE: MAXDT_RAW                       41DC633E5F9C7AE1
NOTE: MAXDT_RAW parsed:               1905064112.479
NOTE: MAXDT_RAW parsed and formatted: 14MAY2020:08:28:32