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!

You should describe more specifically the trouble that you are having. Share error messages if encountered. State specifically what you want the code to do. — floydn
From a cursory view of the code, I see that the %end statement should be moved down and placed after the proc export. That's assuming you want the proc sql and proc export to run for each day in the loop. Of course, I can only assume because you didn't share your expectations of the code. — floydn
By moving the %end statement, your macro will generate a pair of proc sql and proc export statements for each day, but the work.emaildump and the Excel file will be overwritten during each execution of the loop. Regardless of how many days you loop through, those 2 files will only contain the last day in the loop. I won't comment any more until you clarify the purpose and expected result of the program. — floydn
@floydn Thanks for the help - I have now edited the above post - anything else you can help with will be much appreciated - do I need to extend the %do statement (move the %end statement to the bottom of the code) ? — Brecon

Robert Penridge Robert Penridge · Accepted Answer · 2016-03-01T15:26:10

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.

SAS SQL Macro Incremental Date Range

1 Answers