SAS Adding Minutes to a Timestamp Stored in a Macro Variable

I am pulling a large amount of data from an SQL Server through SAS. I would like to pull one minute (or hour) of data at a time, using a loop.

The format of the timestamp is 'yyyymmdd hh:mm:ss.000'.

Usually, I would do something like:

%macro Loop(num_days, tmstmp_begin):
%do i = 0 to &num_days.;
    proc sql;
    ...
    where tmstmp between &tmstmp_begin + &i minutes and &tmstmp_begin (&i+1) minutes;
    quit;
%end
%mend;

But the minute addition is not supported on the server. In Teradata, I can use:

DATEADD(minute, 1, tmsmtmp)

to add a minute to the timestamp, but this will not execute in SAS (it doesn't pass through to the server?):

ERROR: Function DATEADD could not be located.

Anyway I was wondering if there is a neat %sysfunc solution that would help me avoid generating a SAS table of timestamps from which I would read into macro variables, or something else just as silly.

Basically I need to go from:

%let i = 1;
%let tmstmp = '20150801 00:00:00.000'

to:

%put ...something involving tmstmp and i...;
'20150801 00:01:00.000'

Thanks!

Solution

The best way I've found to handle these scenarios is to use a custom datetime format. You can find a link on building them here. I recommend saving the format to a common library so it's always available to your SAS sessions. The format will be:

proc format ;
  picture mssqldt low-high = '''%Y-%0m-%0d %0H:%0M:%0S.000''' (datatype = datetime) ;
run ;

This will take a regular SAS datetime stamp and format it like so (including quotes):

'2015-09-21 15:04:16.000'

The best way to incorporate this into your SAS code is to always keep your dates and date times in their SAS representation, and have separate variables for your SQL server formatted variables. E.g.

Calculate the datetime we want to work with:

%let my_datetime = %sysfunc(datetime());

Create two new variables with the SQL server formatted datetime stamps. I always call mine &sql_start and &sql_end so that they read nicely and I never have to think about it...

%let sql_start = %sysfunc(sum(&my_datetime),mssqldt.);
%let sql_end   = %sysfunc(intnx(minute,&my_datetime,1),mssqldt.);

You can see that to calculate sql_start I used the sum() function within %sysfunc() and passed in the SAS datetime variable. I do it this way because it doesn't change the value of the datetime, and allows me to make use of the second parameter of %sysfunc() which applies the specified format to the value being returned.

For sql_end I used the intnx() function as per usual, and again made use of the second %sysfunc() parameter to format it.

Let's print out the values to see how they look:

%put &sql_start &sql_end;

Gives:

'2015-09-21 15:04:16.000' '2015-09-21 15:05:00'

Then it's simply a case of using it in your code like so:

proc sql;
   ...
   where tmstmp between &sql_start and &sql_end;
quit;

Here's all the code in one spot (assuming you have already defined the format):

%let my_datetime = %sysfunc(datetime());

%let sql_start = %sysfunc(sum(&my_datetime),mssqldt.);
%let sql_end   = %sysfunc(intnx(minute,&my_datetime,1),mysqldt.);

%put &sql_start &sql_end;

proc sql;
   ...
   where tmstmp between &sql_start and &sql_end;
quit;

Now if you want to pull the data one piece at a time, you can compile it all into a loop like this:

%macro get_data(iStart=,iEnd=);

  %local tmp_start tmp_end sql_start sql_end;

  %let tmp_start = &iStart;

  %do %while(&tmp_start le &iEnd);

    %let tmp_end = %sysfunc(intnx(hour,&tmp_start,0,end));

    /* MAKE SURE END OF LOOP ISNT GREATER THAN END DATETIME */
    %if &tmp_end > &iEnd %then %do;
      %let tmp_end = &iEnd;
    %end;


    %let sql_start = %sysfunc(sum(&tmp_start),mssqldt.);
    %let sql_end   = %sysfunc(sum(&tmp_end  ),mssqldt.);

    /* DO SQL HERE */
    %put &sql_start &sql_end;

    /* INCREMENT THE LOOP */
    %let tmp_start = %sysfunc(intnx(hour,&tmp_start,1,beginning));

  %end;

%mend;

Call it for today through to some time tomorrow:

%get_data(iStart=%sysfunc(datetime()),
          iEnd  =%sysfunc(dhms(%sysfunc(date())+1,2,30,13))
         );

The resulting runs are for the following periods:

'2015-09-21 15:25:33.000' '2015-09-21 15:59:59.000'
'2015-09-21 16:00:00.000' '2015-09-21 16:59:59.000'
'2015-09-21 17:00:00.000' '2015-09-21 17:59:59.000'
'2015-09-21 18:00:00.000' '2015-09-21 18:59:59.000'
'2015-09-21 19:00:00.000' '2015-09-21 19:59:59.000'
'2015-09-21 20:00:00.000' '2015-09-21 20:59:59.000'
'2015-09-21 21:00:00.000' '2015-09-21 21:59:59.000'
'2015-09-21 22:00:00.000' '2015-09-21 22:59:59.000'
'2015-09-21 23:00:00.000' '2015-09-21 23:59:59.000'
'2015-09-22 00:00:00.000' '2015-09-22 00:59:59.000'
'2015-09-22 01:00:00.000' '2015-09-22 01:59:59.000'
'2015-09-22 02:00:00.000' '2015-09-22 02:30:13.000'