SAS/SQL - Create SELECT Statement Using Custom Function

Question

UPDATE

Given this new approach using INTNX I think I can just use a loop to simplify things even more. What if I made an array:

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

And then tried to make a loop for each element:

%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

This doesn't quite work, but it captures the idea I want. It could just run the query for each of those values in INTX. Does that make sense?

---

I have a couple of prior questions that I'm merging into one. I got some really helpful advice on the others and hopefully this can tie it together.

I have the following function that creates a dynamic string to populate a SELECT statement in a SAS proc sql; code block:

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;

I've verified that this creates the kind of string I want:

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

This yields:

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

This is exactly what I want the SQL string to be. From prior questions, I believe I need to call this function in a MACRO. Then I want something like this:

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

I am having trouble understanding how to make the code call this macro and interpret as part of the SQL SELECT string. I've tried some of the previous examples in other answers but I just can't make it work. I'm hoping this more specific question can help me fill in this missing step so I can learn how to do it in the future.

Answer 1

Two things:

First, you should be able to use %SYSFUNC to call your custom function.

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

Note that you should not use quotation marks when calling a function via SYSFUNC. Also, you cannot use SYSFUNC with FCMP functions until SAS 9.2. If you are using an earlier version, this will not work.

Second, you have a trailing comma in your select clause. You may need a dummy column as in the following:

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

(Notice that there is no comma before dummy, as the comma is already embedded in your macro.)

---

UPDATE

I read your comment on another answer:

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.

I think I can recommend an easier way to accopmlish what you are doing. First, I'll create a very simple dataset with dates and values. The dates are spread throughout different days, weeks, months and years:

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;

You can now use the INTNX function to create a third column to round the "Opened" column to some time period, such as a 'WEEK', 'MONTH', or 'YEAR' (see this 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;

Output for WEEK:

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

Output for MONTH:

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

Output for YEAR:

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