CODEWITHSUNDEEP
sas
k.dkhk
k.dkhk

Reputation: 530

How to create a datetime macro variable in SAS

%let mydate = "01JUN2021 00:00:00.000"dt;

This does not work. How do I create a datetime macro variable without using proc sql or data step?

Upvotes: 3

Views: 8640

Answers (3)

Robert Penridge
Robert Penridge

Reputation: 8513

The pure macro solution is:

%let mydate = %sysfunc(dhms(%sysfunc(mdy(6,1,2021)), 0, 0, 0));
%put &=mydate; * PRINTS THE UNFORMATTED VALUE STORED;
%put %sysfunc(sum(&mydate), datetime22.); * PRINTS THE DATETIME VALUE FORMATTED;

Output:

MYDATE=1938124800
01JUN2021:00:00:00

You can of course perform the dhms() and mdy() functions on separate lines if that is clearer for you.

Compare this to what your orginal code is doing:

%let mydate="01jan2021:00:00:00"dt;
%put &=mydate;

Prints:

MYDATE="01jan2021:00:00:00"dt

Notice how in your approach the string "01jan2021:00:00:00"dt has been saved into the macro variable, rather than the actual numeric date value 1938124800? Sometimes when you use your approach SAS gets confused when you try to use the value and it is unable to translate the literal to a numeric date value.

Upvotes: 2

Tom
Tom

Reputation: 51566

Your posted macro variable works fine in SAS code.

82   %let mydate = "01JUN2021 00:00:00.000"dt;
83
84   data test;
85    now = datetime();
86    then = &mydate;
87    diff = intck('dtday',then ,now);
88    format now then datetime20. ;
89    put (_all_) (=);
90   run;

now=16JUN2021:08:18:33 then=01JUN2021:00:00:00 diff=15
NOTE: The data set WORK.TEST has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

If you need to use the value in pass through SQL code then you will need to set the macro variable to text that the remote database's implementation of SQL will recognize as a datetime value.

So perhaps

%let myts = timestamp '2021-06-01 00:00:00.000';

Upvotes: 0

Niqua
Niqua

Reputation: 634

try %let mydate = '1Jan2021:0:0:1'dt Note that it uses single quotes & theres no space between date and time

Upvotes: 0

Related Questions