SAS How iterate on date in macro?

Question

I have a macro like this:

%macro loop(report_date);
    /* some sql-code with where-statement on report_date:
         create table with name small_table */
%mend;

Then I want to write a code which creates a table: this table is union of tables where the condition on the varible report_day is true. But my code doesn't work:

%let days_number = 31;
%let Min_Date = '01Jan2018:00:00:00'dt;
/* create table with name big_table */



/*this macro creates a union table */
%macro doInLoop(report_date);
    %loop(&report_date.);

    PROC SQL;
        CREATE TABLE Big_table AS
        SELECT *
        FROM big_table
        UNION ALL
        SELECT *
        FROM small_table;
    QUIT;

%mend;

%macro createTable;

    %local j;
    %do j = 1 to &days_number.;
        %let rep_date = dhms(datepart(&Min_Date.) + j, 0, 0, 0); 

        %if day(rep_date) = 1 %then %doInLoop(%rep_date);
    %end;

%mend;

%createTable;

I have 31 mistakes with messages: "ERROR: The following columns were not found in the contributing tables: j"

Or how can I create a macro, that uses a working macro for one day ("loop"), for certain days from the range?

Thank you.

Reeza Reeza · Accepted Answer · 2018-08-30T00:08:22

Use INTNX() to increment your date, don't do it manually.
You cannot use functions in a %LET statement without %SYSFUNC() otherwise the macro processor cannot tell what is text and what is a function.
Unfortunately you also have more issues than just your date, so I'll show you how to loop it and leave the rest to you.
```
%let rep_date = %sysfunc(intnx(DTDAY, &min_date, 1, 's');
```

DTDAY specifies a day interval for a datetime variable. If you had a date variable the interval would be DAY.

Remember to reference rep_date with the ampersand, &rep_date, otherwise it's just text to SAS.

You may find the sample macros in the macro appendix helpful. One illustrates looping with dates. https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n01vuhy8h909xgn16p0x6rddpoj9.htm&docsetVersion=9.4&locale=en

SAS How iterate on date in macro?

1 Answers