SAS SQL Macro Incremental Date Range

Question

I am currently having trouble using an incrementing data range to output a table that uses a table filled with Emails with JobOffers that have already been renamed using VBA - this is more for my own learning than anything else.

I want the code to loop through the days I can set in a macro command

%EmailDump('01Jan2015'd,'02Jan2015')

And it will run the Macro in the code below and allow me to pull back all emails from within that period and export it (I understand I am over writing the table each time - however this will be exported (each export should be different because it has the macro for EmailStart within it)

So some dummy data for this would look like

Topic EmailStartDate

Job Offer 12/01/2015

Job Offer 25/01/2015

Job Offer 12/05/2015

The Code used is the below

%Macro EmailDump(begindate,endindate);

%do
EmailStart = &begindate.
%to &endindate.
%by 1;

%end;

PROC SQL;

CREATE TABLE WORK.EMAILDUMP AS

SELECT * FROM WORK.EMAILS

WHERE TOPIC = 'JobOffer'

and EmailStartDate = &EmailStart 

;QUIT;

proc export data=work.EMAILDUMP

 dbms=XLSX

 outfile="/p01/Output File &EmailStart " replace;

run;

%Mend EmailDump;

%EmailDump('01Jan2015'd,'02Jan2015'd);

---

The error message looks like the below

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &begindate. ERROR: The %FROM value of the %DO EmailStart loop is invalid. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &endindate. ERROR: The %TO value of the %DO EmailStart loop is invalid. ERROR: The macro EmailDump will stop executing

---

Not sure if anyone can help me with this? Would much appreciate any help!

Answer 1

Below is your code simplified to contain just what is required to reproduce the problem:

%macro emaildump(begindate,endindate);
  %do emailstart = &begindate %to &endindate;
    %put &emailstart;
  %end;
%mend emaildump;

If we call it with literals, we get the message you describe:

%emaildump('01jan2015'd,'02jan2015'd);

Gives:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: &begindate
ERROR: The %FROM value of the %DO EMAILSTART loop is invalid.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: &endindate
ERROR: The %TO value of the %DO EMAILSTART loop is invalid.
ERROR: The macro EMAILDUMP will stop executing.

However, if we pass in raw date values (which are just represented as integers) it works fine. We can calculate the raw date values using the mdy() function. Because we're doing it in the macro language we'll need to wrap mdy() with %sysfunc():

%let start = %sysfunc(mdy(1,1,2015));
%let end   = %sysfunc(mdy(1,2,2015));
%emaildump(&start,&end);

Gives the desired output:

20089
20090

Alternatively, you can also use %sysevalf() to evaluate the literals and convert them to numbers like this:

%let start = %sysevalf('01Jan2015'd);
%let end   = %sysevalf('02Jan2015'd);
%emaildump(&start,&end);

As Quentin points out in the comments below... the reason this occurs is because:

it's a limitation of the fact that %DO statement implicitly calls %EVAL, and %EVAL can't handle date literals.