Search code examples
sasenterprise-guide

Assigning function output to a macro variable

Once I have a value in a variable:

select max(input(record_date, yymmdd10.)) into :LastDate from My_TABLE;

(record_date is a char, the output of above is 23JUL2017).

How would I go about manipulating this value? Let's say I wanted to decrement it by a day. When I do:

%let LastDate = intnx("day", &LastDate, -1);

The value of LastDate becomes literally intnx("day", 21023, -1), while I was hoping for 22Jul2017 or 21022.

Solution

  • If you want to use SAS functions in macro code then they need to wrapped in the %sysfunc() macro function. Also remember that you don't need to add quotes around string literals in macro code since everything is character strings to the macro processor.

    %let LastDate = %sysfunc(intnx(day,&LastDate,-1));

    But that really seems like overkill since dates are just numbers of days. You can just subtract one.

    %let LastDate = %eval(&LastDate -1);

    Or if you are using date literals like '23JUL2017'd then you would need to use the %sysevalf() function instead.

    %let LastDate = %sysevalf(&LastDate -1);

    Of course you could also just subtract the day before making the macro variable.

    select max(input(record_date, yymmdd10.))-1
  into :LastDate trimmed
  from My_TABLE
;