SAS PROC SQL - calculate a column using a value returned from macro program

Question

I would like to automatize the following date calculation: add (or substract) X months to a given numeric variable that represents a date in the form YYYYMM, i.e. 201901 stands for January 2019. Example: 201901 + 13 months = 202002

The following macro returns the desired value (&id_mes_n)

%macro suma_meses(id_mes_ini, n_meses);

%let anio_ini = %sysfunc(floor(&id_mes_ini/100)); /*get year*/
%let mes_ini  = %sysfunc(mod  (&id_mes_ini,100)); /*get month*/
%let aux      = %eval(12*&anio_ini + &mes_ini + &n_meses);

%let anio_n   = %sysfunc(floor(&aux/12)); /*calculate new year*/
%let mes_n    = %sysfunc(mod  (&aux,12)); /*calculate new month*/

%if &mes_n = 0 %then %do;                 /*correction for month 12*/
    %let id_mes_n = %eval(100*(&anio_n-1)+ 12);
%end;
%else %do;
    %let id_mes_n = %eval(100*&anio_n + &mes_n);
%end;

&id_mes_n /*returned value*/

%mend;

%suma_meses(201901, 13) /*returns 202002*/

I would like to use the macro inside a PROC SQL as follows:

PROC SQL;
CREATE TABLE want AS 
SELECT T1.*, %suma_meses(T1.old_date, T1.x_months) AS new_date
FROM have T1
WHERE %suma_meses(T1.old_date, T1.x_months) > 201801 ;
QUIT;

Can this be done? Since this type of calculation is a very recurrent task for people in my area (we are not administrators, engineers, etc.), the idea is to share the macro with other users to simplify syntax. In other words, we want to make code more readable, avoid copy-paste issues, and liberate non-advanced users from dramatic calculation errors XD (especially when there are subqueries involved and X is negative). Such a macro would make our lives easier.

What value is `%EVAL()` supposed to produce when dividing the string `T1.old_date` by 100? For this to work the macro needs to either generate an actual number from an input numeric constant or generate SAS code that calculates the number. from an input variable name. — Tom, Mar 11 '19 at 22:08
Please describe what he macro is trying to do? It looks like it might be trying to treat 6 digit integers as if they represented a year and month value. — Tom, Mar 11 '19 at 22:20
You might want to nip the 'trickiness' in the bud (or as far back in the data flow as possible) and convert your self-encoded date values (yyyy*100 + mm) to SAS date values for downstream processing and analytics. SAS date values are appropriate for date arithmetic and date-based functions, especially `INTCK` `INTNX`. Use a SAS format to display date values in what ever representation you need in output (mm/yyyy, yyyy-mm, mon-year, etc...) — Richard, Mar 12 '19 at 10:38

score 1 · Answer 1 · answered Mar 11 '19 at 22:40

If you are running a relatively recent version of SAS You should share it as an FCMP function rather than a macro function.

proc fcmp allows you to create (and save) user-defined functions that are callable from within datasteps and proc sql (and also via things like %sysfunc()).

Here's an example of an fcmp function that returns a random number between the two numbers specified:

proc fcmp outlib=work.funcs.funcs;
  function randbetween(min,max);
    return ( min + floor( ( 1 + max - min ) * rand("uniform") ) );
  endsub;
run;

Example Usage:

data example;
 do cnt=1 to 5;
   x = randbetween(1,100);
   output;
 end;
run;

Result:

If you get any complaints from SAS about it not recognizing your function you may need to update your options with something along the lines of: options cmplib = (work.funcs);

Thanks Robert. This feature is working in my SAS version. Just for completeness, I think it is useful to point out that SAS recognizes the functions built through `proc fcmp` as system functions, i.e. `%put %sysfunc(randbetween(0, 1));` does write a valid value in the log. — Chuck Ramirez, Mar 12 '19 at 15:03
@ChuckRamirez I believe the functions get executed in a virtual 'data step'. So if anything I'd consider them to be data step functions. When you call `%sysfunc` it is limited to calling functions that are available to the data step... — Robert Penridge, Mar 12 '19 at 16:38

score 1 · Accepted Answer · answered Mar 12 '19 at 01:21

Sound like you are trying to treat numbers like 201,801 as if they represent the first month in the year 2018 and then adding a number of months and generate a number using the same "style".

If you want to do it with digit strings in macro code you could create a macro like this:

%macro add_months_macro(date,months);
%sysfunc(intnx(month,%sysfunc(inputn(&date.01,yymmdd8)),&months),yymmn6)
%mend;

But it you want a method that you can use with values of variables in normal SAS statements then don't use %sysfunc() at all. Instead just use the macro to generate the SAS code to call the functions directly.

%macro add_months_sas(date,months);
input(put(intnx('month',input(cats(&date,'01'),yymmdd8.),&months),yymmn6.),6.)
%mend;

So then your WHERE clause will look like:

WHERE %add_months_sas(T1.old_date, T1.x_months) > 201801

But you should really just convert the digits to actual dates and then use the INTNX() function to add the months. Then there is no need for macros at all.

WHERE intnx('month',T1.old_date, T1.x_months) > '01JAN2018'd

Thanks Tom! Why %sysfunc does not work for my purpose? Does it generate something else rather than a numeric string? — Chuck Ramirez, Mar 12 '19 at 13:12
To the macro processor `t1.old_date` is just a string of characters. The macro processor just generates text. It is useful because you can use it to create dynamic programs by generating text strings that are SAS commands that SAS can compile and run after the macro processor has finished its work. — Tom, Mar 12 '19 at 13:16

SAS PROC SQL - calculate a column using a value returned from macro program

2 Answers2