topic Re: DateTime Macro Variable in SAS Programming

DateTime Macro Variable

twenty7 — Fri, 02 Oct 2020 08:35:19 GMT

I have a dataset which contains date in the following format 'DDMMMCCYY:00:00:00' (01SEP2020:00:00:00). The time part of the field is always ':00:00:00' as there is a separate field with the time.

I am trying to create a macro variable to be able automatically work out the previous days date as this code is due to be scheduled

I would have expected to have been able to get to this using the format DATETIME.

data _null_;
call symput('YTDAY',put(today()-1,datetime.));
run;

This gives me the following '01JAN60:06:09:49' which I was not expecting.

Help?

Thanks

Re: DateTime Macro Variable

Kurt_Bremser — Fri, 02 Oct 2020 09:27:47 GMT

TODAY() results in a date value, which is a count of days; datetimes are counts of seconds.

Do not use a format for your macro variable, store the raw value. Unless you need the macro variable for display instead of calculation/comparison.

For more detailed help, please show the intended use of the macro variable.

Re: DateTime Macro Variable

Aku — Fri, 02 Oct 2020 09:32:05 GMT

With today you are getting number of days since 01-JAN-1960 when you are reading the result as input of Datetime (number of seconds since 01-JAN-1960 00:00:00) you are getting correct result, maybe not what you are expecting.

Re: DateTime Macro Variable

twenty7 — Fri, 02 Oct 2020 09:35:25 GMT

Thanks KurtBremser.

I am using the macro variable in a where statement to ensure I only extract the previous days records.

where APP_DATE = '01SEP2020:00:00:00'DT;

is what I am currently using but the intention was to replace this with the macro variable

Re: DateTime Macro Variable

Kurt_Bremser — Fri, 02 Oct 2020 09:44:19 GMT

Start with working non-macro code that uses the TODAY function:

where datepart(APP_DATE) = today() - 1;

The DATEPART extracts the date from a datetime value.

Then, use a macro variable:

%let yesterday = %eval(%sysfunc(today()) - 1);

/* later in the code */
where datepart(APP_DATE) = &yesterday.;

Re: DateTime Macro Variable

twenty7 — Fri, 02 Oct 2020 10:06:56 GMT

Thank you!