How to create a variable in a dataset resulting from a macro? 1

[TomaHawKPT]

Greetings,

I'm new to SAS, and I'm having trouble adding a new variable (or column) to a dataset (or table). This new column results from a macro that receives as a parameter an existing variable of that dataset.

Below is a simplified example of what I need to do, but I can't figure how to do it.

/* I have a dataset with a column (col1) which value must me passed to the macro */

DATA numbers;
INPUT col1;
DATALINES;
1
2
3
4
5
run;


/* Just an example of a macro */

%macro double(var);
%let doubled = var * 2;
&doubled; * returns the double;
%mend;


/* Now I'd like to add a new column to the data that results from the macro. I'd like to do something like this: */

data doubles
set number;
col2=%double(col1);
run;


/* OR */

proc sql;
create doubles as
select col1, %double(col1) as col2
from numbers;
quit;


Thanks,
Nuno Vidal

 

[klaz2002]

Both your macro and your regular code has to change. Remember that all MACRO is in SAS is a substitution language.
So here is your macro

%macro double(var);
  %let doubled = var * 2;
  &doubled; * returns the double;
%mend;

Here is how I would change it

%macro double(var);
 &var * 2;
%mend;

In your datastep you would do the following

data doubles
  set number;
  col2 = %double(col1);
run;

I hope that this helps you.
Klaz

 

[TomaHawKPT]

Thank you Klaz for your answer, but now I have another question

What if the macro isn't that simple? Instead of the "double" macro I need to apply the macro below.

/*
 * Macro the given a date in the numeric format YYYYMM  (ex: 200212) and 
 * the number of months to add, returns a new numeric date in the same format
 *  - dt_ini    - date in the numeric format YYYYMM
 *  - nr_months - number of months to add to the initial date
 */
%macro add_months(dt_ini, nr_months);
  %let orig_year = %sysevalf((&dt_ini)/100, int);
  %let orig_month = %sysfunc(mod(&dt_ini, 100));

  %let add_years = %sysevalf(&nr_months/12, int);
  %let add_months = %sysfunc(mod(&nr_months, 12));

  %if %eval(&orig_month + &add_months) > 12 %then
    %do;
      %let add_years = %eval(&add_years + 1);
      %let add_months = %eval(&orig_month + &add_months - 12);
      %let orig_month = 0;
    %end;
  %let dt_end = %eval((&orig_year + &add_years) * 100 + (&orig_month + &add_months));

  &dt_end; * Returns the new date;
%mend;

Two questions:
1 - Are there any SAS internal functions to do this easily?
2 - Can I use this macro to create a new column on a table?

I get the error below when running this code:

/* CODE */
DATA dates;
  SET numbers;
  col2=%add_months(200011, col1);
RUN;

/* ERROR */
5319   DATA dates;
5320     SET numbers;
5321     col2=%add_months(200011, col1);
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: col1/12
5321     col2=%add_months(200011, col1);
                                       -
                                       22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, INPUT, PUT.
ERROR: The macro ADD_MONTHS will stop executing.
5322   RUN;

 

[klaz2002]

Tomahawk,
If all you want to do is take a numeric value and convert that into a valid date and then add months to that value and get a result, you don't need this macro code.

Here is what I think you currently have:
1- A numeric value that express the year and month (200101 Jan 2001)
2- Your code needs to be able to add months to that value and return a numeric value with the updated date expressed the same way (in SAS lingo using the same format)

Ok why not use the following line in your code.

DATA dates;
  SET numbers;
  col2=input(put(INTNX('month',input(trim(left(put(col1,8.))),yymmn6.),num_of_months2add),yymmn6.),8.);
RUN;

I assume that your program will 'know' how many months to add based on another variable value. If your want to hard code the number of months to add feel free to do so.

I hope that this helps you in your SAS coding.
Klaz

 

[TomaHawKPT]

Thank you very much Klaz, that's really what I needed.

Now I know another very handy SAS function

Klaz, thank you once again for your time.

Nuno Vidal

 

[sarav1981]

Tomahawk,

A small modification to the original macro code would also resolve your error.

%global dt_end;

%macro add_months(dt_ini, nr_months);
  %let orig_year = %sysevalf((&dt_ini)/100, int);
  %let orig_month = %sysfunc(mod(&dt_ini, 100));

  %let add_years = %sysevalf(&nr_months/12, int);
  %let add_months = %sysfunc(mod(&nr_months, 12));

  %if %eval(&orig_month + &add_months) > 12 %then
    %do;
      %let add_years = %eval(&add_years + 1);
      %let add_months = %eval(&orig_month + &add_months - 12);
      %let orig_month = 0;
    %end;
  %let dt_end = %eval((&orig_year + &add_years) * 100 + (&orig_month + &add_months));

/*&dt_end;*/
%mend;

data dates;
input col1;
datalines;
10
12
1
-2
5
;
run;

data dates_1;
      set dates;
      call execute('%add_months(200612,'||put(col1,2.)||')');
      col2 = resolve('&dt_end');
run;