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
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
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

Tom Tom · Accepted Answer · 2019-03-12T01:21:10

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

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

2 Answers