DATA Step, Macro, Functions and more

Passing a SQL Query into a Macro Loop

Reply
Contributor
Posts: 27

Passing a SQL Query into a Macro Loop

I am trying to write a macro that loops 2 (or more) times and each loop runs a Proc SQL query.  This would be very simple if I wanted to just do it once but I want to automate it so the macro will work for many programs.  Each program is in a similar format but the Proc SQL query is different.

An example for doing it once is below.  What the macro is doing is looping 2 times where each time it changes the macro variables for the start and end date.  These dates are then used in the Proc SQL step.  This produces 2 data sets where the first is from last months data and the other is from the month before that.

What I will have is a program that runs an SQL query once.  I want to be able to call a macro that will use that query and loop it like below.  What I don't want to do is have to is add the code around the query each time.  Is there a way to pass all the code for the SQL procedure through to the macro (an example of this is Option 1 below)?  Otherwise, is there a way to create a macro that is the first part of the loop, call that before the query, and then create another macro that is basically an %END statement at the end of the query?  That way instead of adding ~10 lines of code to each program I could just add 2 (option 2).

I tried to explain that as clear as I could, if it is not clear let me know and I can try to explain further.

Macro example as I currently have it:

/* Macro to get past 2 months worth of data, one month per data set */

%macro Test;

%DO i=1 %TO 2;

%let i1=%sysfunc(sum(-&i,+1));

/* Create Date Variables */

Data _null_;

  Today = Today() ;

  Call symput("Test_Start_Date",        Cat("'", put(intnx("Day",intnx("Month", Today, -&i, "B"), 0),Date9.),"'d"));

  Call symput("Test_End_Date",          Cat("'", put(intnx("Day",intnx("Month", Today, &i1, "B"), -1),Date9.),"'d"));

run;

proc sql;

create table example&i as

/* INSERT QUERY DETAILS HERE */

Where date>=&Test_Start_Date and date<&Test_End_Date

;quit;

%END;

%mend;

Example of option 1:

....

proc sql;

....

;quit;

%LoopMacro(..., SQL Procedure?,...)

Example of option 2:

%First_Half_of_Loop

proc sql;

....

;quit;

%Last_Part_of_Loop

Valued Guide
Posts: 632

Re: Passing a SQL Query into a Macro Loop

First the easy part:

Let's simplify the creation of the macro variables that hold the dates.

  Call symputx("Test_Start_Date", intnx("Month", Today, -&i);    

Notice that I used the SYMPUTX routine and eliminated the 'B' (the default).

Second

The WHERE clause in your SQL step depends on the macro loop so why would you want it to only execute once?  I think that I do not understand.

Perhaps you want to write the code once and execute it twice - which is what you have done here.

Contributor
Posts: 27

Re: Passing a SQL Query into a Macro Loop

I do want it to execute twice, I was hoping there was a way that I could insert one or two lines of code (the looping part of the macro) around the SQL query so that when someone else wants to update their code for this they would only need to add in one or two macro call statements.

I realize it would be easy enough to just hard code the macro in the different programs but unfortunately I need a bit more elegant solution.  I do have an idea to just change the date range variables to select all of the data in one dataset and then later separate the results of the query into different tables given the proper date range with a different macro.  That would probably be the easiest solution.

Super Contributor
Posts: 1,636

Re: Passing a SQL Query into a Macro Loop

how about:

%macro test;

proc sql;

%do i=1 %to 2;

create table example&i as

/* INSERT QUERY DETAILS HERE */

Where date>=intnx('month',today(),-&i,'b') and date<intnx('month',today(),-&i+1,'e');

%end;

quit;

%ment test;

Contributor
Posts: 52

Re: Passing a SQL Query into a Macro Loop

I think this is what you are asking for. I have tested the macro date setting section. For the PROC SQL code, I use this construction a lot.

%MACRO test;

   %do i = 1 %to 2;

      %let shift = %eval(&i * -1 + 1);

      %let test_start_date = %sysfunc(intnx(month, "&sysdate"d, &shift, b), date9.);

      %let test_end_date = %sysfunc(intnx(month, "&sysdate"d, &shift, e), date9.);

      create table example&i as

            /* INSERT QUERY DETAILS HERE */

         where

            "&test_start_date"d <= date <= "&test_end_date"d;

   %end;

%MEND;

proc sql;

   %test;

quit;

I hope this helps.

Contributor
Posts: 27

Re: Passing a SQL Query into a Macro Loop

Thanks for the help, the only issue with this is there will be more in the where statement depending on the query so that wouldn't always work.

Contributor
Posts: 52

Re: Passing a SQL Query into a Macro Loop

Okay,  here's the same idea, packaged in a different wrapper. You can play around with this. I have not tested this code.

%MACRO set_dates(i);

   %let shift = %eval(&i * -1 + 1);

   %let test_start_date = %sysfunc(intnx(month, "&sysdate"d, &shift, b), date9.);

   %let test_end_date = %sysfunc(intnx(month, "&sysdate"d, &shift, e), date9.);

   %let dataset_name = example&i;

   %let date_string = "&test_start_date"d <= date <= "&test_end_date"d;

%MEND;

%global test_start_date test_end_date dataset_name date_string;

proc sql;

   %set_dates(1);

  

   create table &dataset_name as

         /* INSERT QUERY DETAILS HERE */

      where

         /* INSERT other conditions HERE */ and

        &date_string

         ;

   %set_dates(2);

  

   create table &dataset_name as

         /* INSERT QUERY DETAILS HERE */

      where

         /* INSERT other conditions HERE */ and

        &date_string

         ;

quit;

Valued Guide
Posts: 632

Re: Passing a SQL Query into a Macro Loop

For some reason we often make SAS dates more complicated than necessary when working with them in the macro language.

Remember that a date value is just a number.  The date constant form such as '31Jul2012'd is actually a function call that is translated to a number, 19205.  Therefore there is no reason to convert a SAS date  to a text string and then convert it back to a number.  For instance:

     %let test_end_date = %sysfunc(intnx(month, "&sysdate"d, &shift, e), date9.);

For today's date the value of &test_end_date is 31jul2012. and when used in the constant "&test_end_date"d resolves to 19205.

We can simplify by going directly to the actual date.  The %LET becomes: 

    %let test_end_date = %sysfunc(intnx(month, "&sysdate"d, &shift, e));

&test_end_date now contains 19205 and it can be used directly in a comparison:

     %let date_string = &test_start_date <= date <= &test_end_date;

Contributor
Posts: 52

Re: Passing a SQL Query into a Macro Loop

I take the extra time to have the date values format as strings just so that when I read the log output, I can be sure that the correct dates were applied. For me, transparency is an important consideration.

New Contributor
Posts: 3

Re: Passing a SQL Query into a Macro Loop

I had a question relating to the request. Is'nt such a macro very  inefficient. let us say you want to extract the past 6 months of data. In such a case you loop 6 times. Then  you would be reading the original dataset  6 times for each create  of a new datset / table will go against the original table. Or Am i missing something? it is much easier to code it using base sas instead of trying to use SQL.

Super User
Posts: 3,102

Re: Passing a SQL Query into a Macro Loop

I agree with Puneet. Your original post says you want to create two datasets, one for last month and one for the month before that. A single DATA step can produce both tables (or greater than two) just passing through your data once. It would be more efficient to do it this way:

data last_month

       month_before_last;

  set in_dataset;

  where... conditions for both months.....;

  if ...conditions for last month... then output last_month;

  else output month_before_last;

run;

If you wanted more than two output datasets then you could build in macro logic to do that. 

New Contributor
Posts: 3

Re: Passing a SQL Query into a Macro Loop

the code attached will work.

The code is lsightly longer then necessary but it allows the user to see at every step what is happening.

I was unable to cut and paste it in the query here.

Attachment
Regular Contributor
Posts: 241

Re: Passing a SQL Query into a Macro Loop

Since macro can generate anything from nothing to multiple data and proc steps, it is not trivial to design an "elegant" macro. I prefer writing short utility macros, but it is just me. Hope this helps a bit.

%*-- returns "between (first day of month) and (last day of month)" --*;
  %macro isInMonth(ofDate=%sysfunc(today()));
     %local first last;
     %let first = %sysfunc(intnx(mon,&ofDate,0,b));
     %let last  = %sysfunc(intnx(mon,&ofDate,0,e));
     %*;between &first and &last
  %mend  isInMonth;
 
 
%*-- test data --*;
  options mprint;
  data one;
    do date = "01jan2012"d to "31aug2012"d;
      output;
    end;
    format date date10.;
  run;
 
%*-- usage example -- I prefer this --*;
  %let today = %sysfunc(today());
  %*-- same day last month --*;
  %let sdlm1 = %sysfunc(intnx(mon, &today, -1, s));
  %let sdlm2 = %sysfunc(intnx(mon, &today, -2, s));
 
  data d1 d2;
    set one;
    where date %isInMonth(ofDate=&sdlm1) or
          date %isInMonth(ofDate=&sdlm2);
    select(month(date));
      when(month(&sdlm1)) output d1;
      when(month(&sdlm2)) output d2;
      otherwise;
    end;
  run;
 
 
%*-- if you insist on two sql procs wrapped up in a macro... --*;
  %macro extract(select, outPrefix=ex, date=%sysfunc(today()));
    %local sdlm1 sdlm2 i;
    %do i = 1 %to 2;
      %let sdlm&i = %sysfunc(intnx(mon, &date, -&i, s));
      proc sql;
        create table &outPrefix.&i as
        %unquote(&select)
        where date %isInMonth(ofDate=&&sdlm&i);
      quit;
    %end;
  %mend  extract;
 
  %*-- usage example --*;
  %extract(%nrstr(
    select * from one
  ))
 
  %*-- check --*;
  proc compare base=d1 compare=ex1; run;
  proc compare base=d2 compare=ex2; run;
  %*-- on lst, in part
  NOTE: No unequal values were found. All values compared are exactly equal.

  NOTE: No unequal values were found. All values compared are exactly equal.
  --*;

Ask a Question
Discussion stats
  • 12 replies
  • 3002 views
  • 0 likes
  • 7 in conversation