sql - SAS/SQL - 使用自定义函数创建 SELECT 语句

Question

更新

鉴于这种使用 INTNX 的新方法，我认为我可以使用循环来进一步简化事情。如果我制作一个数组怎么办:

data;
    array period [4] $ var1-var4 ('day' 'week' 'month' 'year');
run;

然后尝试为每个元素创建一个循环:

%MACRO sqlloop;
  proc sql;
    %DO k = 1 %TO dim(period);  /* in case i decide to drop something from array later */
      %LET bucket = &period(k)
      CREATE TABLE output.t_&bucket AS (
        SELECT INTX( "&bucket.", date_field, O, 'E') AS test FROM table);
    %END
  quit;
%MEND
%sqlloop

这不太行，但它捕获了我想要的想法。它可以只对 INTX 中的每个值运行查询。这有意义吗？

---

我有几个先前的问题正在合并为一个。我从其他人那里得到了一些非常有用的建议，希望这可以将它们联系在一起。

我有以下函数创建一个动态字符串来填充 SAS proc sql; 代码块中的 SELECT 语句:

proc fcmp outlib = output.funcs.test;
    function sqlSelectByDateRange(interval $, date_field $) $;
        day = date_field||" AS day, ";
        week = "WEEK("||date_field||") AS week, ";
        month = "MONTH("||date_field||") AS month, ";
        year = "YEAR("||date_field||") AS year, ";

        IF interval = "week" THEN
            do;
                day = '';
            end;
        IF interval = "month" THEN
            do;
                day = '';
                week = '';
            end;
        IF interval = "year" THEN
            do;
                day = '';
                week = '';
                month = '';
            end;
        where_string = day||week||month||year;
    return(where_string);
    endsub;
quit;

我已经验证这会创建我想要的字符串类型:

data _null_;
    q = sqlSelectByDateRange('month', 'myDateColumn');
    put q =;
run;

这会产生:

q=MONTH(myDateColumn) AS month, YEAR(myDateColumn) AS year,

这正是我想要的 SQL 字符串。根据之前的问题，我认为我需要在 MACRO 中调用此函数。然后我想要这样的东西:

%MACRO sqlSelectByDateRange(interval, date_field);
  /* Code I can't figure out */
%MEND

PROC SQL;
  CREATE TABLE output.t AS (
    SELECT 
      %sqlSelectByDateRange('month', 'myDateColumn')
    FROM
      output.myTable
  );
QUIT;

我无法理解如何让代码调用此宏并将其解释为 SQL SELECT 字符串的一部分。我已经在其他答案中尝试了一些前面的例子，但我就是无法让它发挥作用。我希望这个更具体的问题可以帮助我填补这个缺失的步骤，以便我将来可以学习如何做。

Answer 1

两件事:

首先，您应该能够使用 %SYSFUNC调用您的自定义函数。

%MACRO sqlSelectByDateRange(interval, date_field);
    %SYSFUNC( sqlSelectByDateRange(&interval., &date_field.) )
%MEND;

请注意，通过 SYSFUNC 调用函数时不应使用引号。另外，you cannot use SYSFUNC with FCMP functions until SAS 9.2 .如果您使用的是早期版本，这将不起作用。

其次，您的 select 子句中有一个尾随逗号。您可能需要一个虚拟列，如下所示:

PROC SQL;
  CREATE TABLE output.t AS (
    SELECT 
      %sqlSelectByDateRange('month', 'myDateColumn')
      0 AS dummy
    FROM
      output.myTable
  );
QUIT;

(注意 dummy 之前没有逗号，因为逗号已经嵌入到您的宏中。)

---

更新

我阅读了您对另一个答案的评论:

I also need to be able to do it for different date ranges and on a very ad-hoc basis, so it's something where I want to say "by month from june to december" or "weekly for two years" etc when someone makes a request.

我想我可以推荐一种更简单的方法来完成您正在做的事情。首先，我将创建一个包含日期和值的非常简单的数据集。日期分布在不同的日、周、月和年中:

DATA Work.Accounts;

    Format      Opened      yymmdd10.
                Value       dollar14.2
                ;

    INPUT       Opened      yymmdd10.
                Value       dollar14.2
                ;

DATALINES;
2012-12-31  $90,000.00
2013-01-01 $100,000.00
2013-01-02 $200,000.00
2013-01-03 $150,000.00
2013-01-15 $250,000.00
2013-02-10 $120,000.00
2013-02-14 $230,000.00
2013-03-01 $900,000.00
RUN;

您现在可以使用 INTNX 函数创建第三列以将“Opened”列四舍五入到某个时间段，例如 'WEEK'，'MONTH'，或 'YEAR'(参见 complete list):

%LET Period = YEAR;

PROC SQL NOPRINT;

    CREATE TABLE Work.PeriodSummary AS
    SELECT   INTNX( "&Period.", Opened, 0, 'E' ) AS Period_End FORMAT=yymmdd10.
           , SUM( Value )                        AS TotalValue FORMAT=dollar14.
    FROM     Work.Accounts
    GROUP BY Period_End
    ;

QUIT;

WEEK 的输出:

Period_End   TotalValue
2013-01-05     $540,000
2013-01-19     $250,000
2013-02-16     $350,000
2013-03-02     $900,000

MONTH 的输出:

Period_End   TotalValue
2012-12-31      $90,000
2013-01-31     $700,000
2013-02-28     $350,000
2013-03-31     $900,000

YEAR 的输出:

Period_End   TotalValue
2012-12-31      $90,000
2013-12-31   $1,950,000