Conversion of SAS Macro Variable to time stamp

Question

I am trying to convert SAS Macro variable to timestamp and stumped while conversion. The code which I am using is given below.

%let date = '03/15/2013';
%put %sysfunc(inputn(&date,datetime26.6));

Error which I am getting is

WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range. NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The result of the operations have been set to a missing value.

Please let me know if someone knows answers to this.

Joe · Accepted Answer · 2013-09-04T16:42:26.037

That is not a DATETIME, that is a DATE format (to INPUT, which depends on the incoming data, not the outgoing). You also need to remove the quotes, SYSFUNC treats quotes as characters, not as string delimiters.

%let date = 03/15/2013;
%put %sysfunc(inputn(&date,MMDDYY10.));

To actually create the datetime, you need to use PUT:

%let date = 03/15/2013;
%put %sysfunc(putn(%sysfunc(dhms(%sysfunc(inputn(&date,MMDDYY10.)),0,0,0)),datetime26.));

However, the better way to do this if you can is to use a date constant...

%let date=15MAR2013;
%put "&date."d;

score 2 · Answer 2 · answered Sep 04 '13 at 14:08

2

Joe is mostly correct. If you want a datetime string of midnight 3/15/13, then use

%let date = 03/15/2013;
%put %sysfunc(putn(%sysfunc(dhms(%sysfunc(inputn(&date,MMDDYY10.)),0,0,0)),datetime26.));

Just using PUTN on a date string to "convert" a date to datetime will convert the number of days from epoch (01JAN1960) to the number of seconds from epoch.

answered Sep 04 '13 at 14:08

DomPazz

12,415
17
23

Thanks DomPazz . The trick of "DHMS" is new to me and obviously both of you are very correct. Unfortunately I am getting dates from a DB which enforces this. – LonelySoul Sep 04 '13 at 14:18
Eep, that's what I get for answering during a boring IT conference call and not testing. Thanks for the catch. – Joe Sep 04 '13 at 16:41
@DomPazz Hi Dom - `%sysfunc` supports 2 arguments, the second of which will apply a format to the result. This means you don't need to call `putn()` or `putc()` to format the result anymore. I think it was added in version 9.x... e.g. `%put %sysfunc(dhms(%sysfunc(inputn(&date,MMDDYY10.)),0,0,0),datetime26.);` – Robert Penridge Sep 04 '13 at 22:26
@RobPenridge good to know! I think I have seen that used but I am still a little old school on some things like that. – DomPazz Sep 05 '13 at 02:08

score 2 · Answer 3 · answered Sep 04 '13 at 14:34

My preference for working with dates in macro variables is to store the actual numeric value in the macro variable, and if I need to view/print the formatted value then assign a format to it on the fly:

%let date = %sysfunc(mdy(3,15,2013));
%put %sysfunc(putn(&date,date9.));

That allows you to use it in comparisons like the below (which I find is the most common task):

data xx;
  set something;
  where datefield = &date;
run;

Conversion of SAS Macro Variable to time stamp

3 Answers3