CODEWITHSUNDEEP
sassas-macroproc-sql
Chuck Ramirez
Chuck Ramirez

Reputation: 255

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

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.

Upvotes: 3

Views: 1416

Answers (2)

Tom
Tom

Reputation: 51566

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

Upvotes: 1

Robert Penridge
Robert Penridge

Reputation: 8513

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:

Obs cnt x
1   1   8
2   2   93
3   3   98
4   4   97
5   5   12

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

Upvotes: 1

Related Questions