Run macro (with a value from column as parameter) for every observation

[filippo_kow]

 Hi guys,

I have a problem.

In my dataset I would like to run a macro for every observation, and pass to that macro a value from a particular column.

So I would like to do something like that:

data test;
 set some_data_set;
 /* I have a column, let's say "VALUE", and for every observation I would like to call a macro with this VALUE as a parameter. I am trying to do something like that: */ 

/* create a macro variable that will store value of "VALUE" column for every observation: */
 call symputx("my_variable", VALUE, 'g');

/* now I would like to pass "my_variable" as a parameter to some macro. 
And that macro should be called for every observation as well: */
 %some_macro(&my_variable.);

/* In other words, for every observation I would like to run a macro with a value from some column as a parameter */
run;

The first problem is that I can't use macro variable created by call symput in the same data step that it was created. But I don't have any idea, how could I do this 😞

I would be very grateful for any help!!!

 Thanks in advance,

 Filip

[filippo_kow]

 Hi again,

I just realized that I should use call execute routine in the following way:

data test;
 set some_data_set;
 call execute('%some_macro('||VALUE||');');
run;

But here comes another problem. Let's assume that macro %some_macro does some modifications, comparisons and so on (based on columns from some_data_set), so it looks something like this:

%macro some_macro();
 if column_1 > column_2 then do;
  column_3 = 'test';
 end;
/* and so on */
%mend;

Unfortunately there is an error message in the log: ERROR 180-322: Statement is not valid or it is used out of proper order.. It looks that I can not use for example comparisons. I totally don't have a clue how can I implement such logic. Any help would be appreciated 🙂

 Best,

 Filip

[PaigeMiller]

There's no way anyone can take this fragmentary piece of the SAS log and make sense out of it.

First, run this line of code to turn on macro debugging.

options mprint;

Then run the code again. Show us the ENTIRE log for this macro, and not selected fragments. (If it's a really long macro, then show us the 50 lines or so before the error). Use the </> icon and paste the log as text into the window that appears.

And from now on, @filippo_kow please do not show us fragmentary logs, show us the entire log (without us having to ask). Thanks!

[Quentin]

Hi,

Yes, CALL EXECUTE is a good tool for this.  But I'm confused as to what your %SomeMacro is doing.  If you code:

data test;
 set some_data_set;
 call execute('%some_macro('||VALUE||');');
run;

The SAS code generated by %SomeMacro  will execute AFTER the step that writes work.test has completed.   Typically, %SomeMacro would generate one or more DATA/PROC steps.  But the code generated by the macro cannot is not part of the same step where you invoke CALL EXECUTE.

Perhaps if you post a little example of what you are trying to do, including work.some_data_set with 3-4 records, and a simple version of %SomeMacro, that will help clarify things.

[filippo_kow]

 Hi Paige and Quentin,

Many thanks for your reply!

In general, I am trying trying to do quite "easy" thing: during processing the dataset take the value from one particular column -> run macro with value of that column as a parameter -> macro should modify other column (based on some conditions).

But step by step:

• This is a sample code that you can run:

options mprint;

/* create input dataset */
data a;
	infile datalines; 
	input name $ value1 value2 value3;
	datalines;
test1 10 1 0
test2 20 22 0
test3 30 3 0
run;

/* re-calculate value3 - set to missing or multiply value2 (passed by parameter in below datastep) by 2 */
%macro do_sth(var);
	if value1 < var then do;
		value3 = .;
	end;
	else do;
		value3 = var * 2;
	end;
%mend;

/* create new dataset with new value3 column */
data b;
	set a;
	call execute('%do_sth('!!value2!!');');
run;

• This is a piece of log after invoking data b:

28         data b;
29         	set a;
30         	call execute('%do_sth('!!value2!!');');
31         run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      30:27   
MPRINT(DO_STH):   if value1 < var then do;
MPRINT(DO_STH):   value3 = .;
MPRINT(DO_STH):   end;
MPRINT(DO_STH):   else do;
MPRINT(DO_STH):   value3 = var * 2;
MPRINT(DO_STH):   end;
MPRINT(DO_STH):   if value1 < var then do;
MPRINT(DO_STH):   value3 = .;
MPRINT(DO_STH):   end;
MPRINT(DO_STH):   else do;
MPRINT(DO_STH):   value3 = var * 2;
2                                                          The SAS System                               15:43 Tuesday, July 19, 2022

MPRINT(DO_STH):   end;
MPRINT(DO_STH):   if value1 < var then do;
MPRINT(DO_STH):   value3 = .;
MPRINT(DO_STH):   end;
MPRINT(DO_STH):   else do;
MPRINT(DO_STH):   value3 = var * 2;
MPRINT(DO_STH):   end;
NOTE: There were 3 observations read from the data set WORK.A.
NOTE: The data set WORK.B has 3 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              671.25k
      OS Memory           27496.00k
      Timestamp           07/19/2022 11:36:05 PM
      Step Count                        37558  Switch Count  2
      

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1         + if value1 < var then do;
            __
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
1         +                            value3 = .;
                                       ______
                                       180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
1         +                                         end;
                                                    ___
                                                    180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
1         +                                               else do;
                                                          ____
                                                          180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
1         +                                                          value3 = var * 2;
                                                                     ______
                                                                     180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: Line generated by the CALL EXECUTE routine.
1         +                                                                             end;
                                                                                        ___
3                                                          The SAS System                               15:43 Tuesday, July 19, 2022

                                                                                        180

 and so on...

If you need any further clarification, just let me know.

 Thanks!!!

 Filip

[PaigeMiller]

CALL EXECUTE runs after the DATA step finishes. The code that CALL EXECUTE generates must be legal valid working SAS code.

The first line generated by CALL EXECUTE is

if value1 < var then do;

and this is only valid inside a SAS data step, but remember, the DATA step has finished.

This leads me to ask why don't you just put this code directly into the DATA step? In other words, not inside a macro, not inside CALL EXECUTE. What is it about this code that you haven't told us that makes you think you need a macro (or CALL EXECUTE)? Is your macro %do_sth just a simple example, not the real macro you want to use?

[filippo_kow]

Hmm, that's a bad news 😞 The code that I placed is just an example - I mean in "real" life I have much more complicated code, but the idea is exactly as in my example.

So my question is: is it possible in SAS to dynamically (I mean observation by observation) run macro with a column value as a parameter and based on that manipulate other column(s)? From my current knowledge it looks that no matter what you use (call symput, call execute), it runs after the datastep is completed, so there is no solution to my problem...

 Thanks in advance!  

[PaigeMiller]

So my question is: is it possible in SAS to dynamically (I mean observation by observation) run macro with a column value as a parameter and based on that manipulate other column(s)?

You can run DATA step code that is dynamic and takes a value in one column and manipulates other columns, and this can change every observation in the data set. You still haven't given a real explanation of why this is not sufficient for you, and why a macro is necessary.

[Quentin]

Did you consider using ARRAYS instead of a macro? This feels like a setting where a dynamic array approach might work.

[filippo_kow]

 Hi guys,

The real example is that I have ca. 15 columns with numerical value. Then, I need to find the minimal one, I am doing it using arrays:

 lowest_value = min(of columns[*]);
 index_lowest_value = whichn(lowest_value, of columns[*]);
 column_lowest_value = vname(columns[index_lowest_value]);

Then I need to implement some logic for columns other that column_lowest_value (using some loops and so on), so I try run the macro with column_lowest_value as a parameter - something like that:

call symput('lowest', column_lowest_value, 'g');
%do_sth(&lowest.);

/* or: */
call execute('%do_sth('||lowest||');');

The problem is that I need to do that dynamically, during processing. I am thinking about some workaround, but no idea so far 😞

[andreas_lds]

---

@filippo_kow wrote:

 Hi guys,

The real example is that I have ca. 15 columns with numerical value. Then, I need to find the minimal one, I am doing it using arrays:

 lowest_value = min(of columns[*]);
 index_lowest_value = whichn(lowest_value, of columns[*]);
 column_lowest_value = vname(columns[index_lowest_value]);

Then I need to implement some logic for columns other that column_lowest_value (using some loops and so on), so I try run the macro with column_lowest_value as a parameter - something like that:

call symput('lowest', column_lowest_value, 'g');
%do_sth(&lowest.);

/* or: */
call execute('%do_sth('||lowest||');');

The problem is that I need to do that dynamically, during processing. I am thinking about some workaround, but no idea so far 😞

---

Don't insert macro-stuff into not fully working sas code, the macro-code will make things more complex, only.

You should start explaining what you want to achieve at the end, so that we can suggest something working.

[PaigeMiller]

---

@filippo_kow wrote:

 Hi guys,

The real example is that I have ca. 15 columns with numerical value. Then, I need to find the minimal one, I am doing it using arrays:

 lowest_value = min(of columns[*]);
 index_lowest_value = whichn(lowest_value, of columns[*]);
 column_lowest_value = vname(columns[index_lowest_value]);

Then I need to implement some logic for columns other that column_lowest_value (using some loops and so on), so I try run the macro with column_lowest_value as a parameter - something like that:

call symput('lowest', column_lowest_value, 'g');
%do_sth(&lowest.);

/* or: */
call execute('%do_sth('||lowest||');');

The problem is that I need to do that dynamically, during processing. I am thinking about some workaround, but no idea so far 😞

---

You can certainly do an array loop, where you tell the loop to not execute if it is the column with lowest value.

data example;
input id x1 x2 x3 x4;
cards;
1 5 4 3 2
2 -1 0 1 2
;

data want;
    set example;
    array x x1-x4;
    /* Find lowest value in each row */
    min_value=min(of x{*});
    /* Do things to the columns that don't have min value in each row */
    do i=1 to dim(x);
        if x(i)^=min_value then x(i)=x(i)+1000;
    end;
run;

This is a great example of the XY problem in action. The user wants to do a somewhat unusual action, and the solution (macros or CALL EXECUTE) has already been determined. This wastes a lot of time, as the simpler solution never gets discussed, and indeed requests to explain the larger problem (that might lead to a simpler solution) go un-answered. @filippo_kow from now on, please do not force us to repeatedly request an explanation of the problem, and avoid providing that explanation of the problem. From now on, explain the problem at the beginning, rather than saying you need a macro or CALL EXECUTE solution.

[Quentin]

Hi,

This still feels like an array problem to me.  If Paige's example doesn't help, please post a full DATA step code showing what you are doing.  That is, replace the macro call with the SAS code you are hoping the macro would generate.

That said, it's important to know why the code you are trying (with the macro call) cannot work.  It's a timing issue essential to understanding the macro language.

When you submit a DATA step:

data test;
 set some_data_set;
run;

The code is is first compiled. During compilation the Program Data Vector is created, and other key things.  After it compiles, it is executed During execution it reads data records and processes them.

When you submit a step that includes a macro call:

data test;
 set some_data_set;
 %some_macro(&my_variable.);
run;

The macro is executed  *before* the DATA step has compiled.  The macro language is a pre-processor.  It's job is to generate SAS code.

Thus you cannot pass the value of a data step variable to a macro call, because when the macro executes the data step variable does not exist yet.

HTH

[Sajid01]

First, from what that has been posted in the question, there is no need to convert to create a macro variable and pass it on to another function or macro. If there is something more that what that has been posted please be clear.

Second, the macro variable will be available after the data step has completed execution.

[Tom]

If you want to use the distinct values of a variable to generate code there is no need to use the macro language (other than %INCLUDE statement) or CALL EXECUTE().

Write the code to a file and use %INCLUDE to run it.

Say you have a macro named FRED that has one input parameter named VALUE.  And you have a SAS dataset named HAVE with all of the possible values of VALUE that you need to use with the macro.

filename code temp;
data _null_;
  set have;
  file code;
  put '%fred(' value= ')' ;
run;

Now you can use that code. 

If the macro generates complete data and/or PROC steps then just run it.

%include code / source2;

If the macro generates just some code that could be part of a data step (like IF statements) then include it in the data step where you need it to run.

data new;
  set old;
%include code / source2;
run;