sql - SAS 将分钟添加到存储在宏变量中的时间戳

Question

我正在通过 SAS 从 SQL Server 中提取大量数据。我想使用循环一次提取一分钟(或一小时)的数据。

时间戳的格式是'yyyymmdd hh:mm:ss.000'。

通常，我会做这样的事情:

%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;

但是服务器不支持分钟加法。在 Teradata 中，我可以使用:

DATEADD(minute, 1, tmsmtmp)

向时间戳添加一分钟，但这不会在 SAS 中执行(它不会传递到服务器？):

ERROR: Function DATEADD could not be located.

无论如何，我想知道是否有一个简洁的 %sysfunc 解决方案可以帮助我避免生成一个 SAS 时间戳表，我可以从中读取宏变量，或者其他一些愚蠢的东西。

基本上我需要从:

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

到:

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

谢谢!

Answer 1

我发现处理这些情况的最佳方法是使用自定义日期时间格式。您可以找到有关构建它们的链接 here .我建议将该格式保存到一个公共(public)库中，以便它始终可用于您的 SAS session 。格式为:

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

这将采用常规 SAS 日期时间戳并将其格式化(包括引号):

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

将其合并到您的 SAS 代码中的最佳方法是始终将您的日期和日期时间保留在它们的 SAS 表示中，并为您的 SQL 服务器格式化变量设置单独的变量。例如

计算我们要使用的日期时间:

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

使用 SQL 服务器格式的日期时间戳创建两个新变量。我总是调用我的 &sql_start 和 &sql_end 以便它们读起来很好，我永远不必考虑它......

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

您可以看到，为了计算 sql_start，我在 %sysfunc() 中使用了 sum() 函数，并传入了 SAS datetime 变量.我这样做是因为它不会更改日期时间的值，并允许我使用 %sysfunc() 的第二个参数，它将指定的格式应用于返回的值。

对于 sql_end，我像往常一样使用了 intnx() 函数，并再次使用了第二个 %sysfunc() 参数来格式化它。

让我们打印出这些值以查看它们的外观:

%put &sql_start &sql_end;

给予:

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

那么这只是在您的代码中使用它的一个例子，如下所示:

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

---

这里是所有代码(假设您已经定义了格式):

%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;

---

现在如果你想一次提取一个数据，你可以像这样把它全部编译成一个循环:

%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;

从今天到明天某个时间调用它:

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

生成的运行时间如下:

'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'