topic Re: %do %to macro doenst stop after match a condition in SAS Enterprise Guide

%do %to macro doenst stop after match a condition

erickdt — Wed, 14 Apr 2021 20:29:59 GMT

hi there!
I've a problem within my macro:
My data has 200k+ lines and my itention is:
When my looping variable is greater then
my variable QT_PARCELA (numeric type per lina) all my values in colluns after that should be 0, however my macro doens't stop when the variable DU is greater then QT_PARCELA, anyone can help me pls? 😄

%MACRO LOOP();
DATA TESTE;
SET BASE_INFO_EXP2;
%DO DU=0 %TO 6;
%IF &DU. LE QT_PARCELA %THEN %DO;
%IF &DU. EQ 0 %THEN %DO;
RISCO_M&DU. = VL_PRODUCAO;
JUROS_M&DU. = 0;
AMORT_M&DU. = 0;
%END;
%ELSE %DO;
%LET DU_AUX = %SYSFUNC(SUM(&DU. -1));
JUROS_M&DU. = RISCO_M&DU_AUX. * (TAXA / 100);
AMORT_M&DU. = VL_PARCELA - JUROS_M&DU.;
RISCO_M&DU. = RISCO_M&DU_AUX. - AMORT_M&DU.;
%END;
%END;
%ELSE %DO;
JUROS_M&DU. = 0;
AMORT_M&DU. = 0;
RISCO_M&DU. = 0;
%END;
%END;
RUN;
%MEND;

%LOOP();

Re: %do %to macro doenst stop after match a condition

PaigeMiller — Wed, 14 Apr 2021 20:44:12 GMT

First, you can help yourself by indenting your code properly so it is much easier to see where each %DO statement begins and ends.

Second, you can help yourself by using ARRAY statements in DATA steps, rather than macro loops. These are easier to construct as they rely entirely on DATA step commands, and also easier to debug.

Next, if you still have to write this a macro (which I am skeptical of) you can also help yourself by turning on diagnostics, via this command, and then running your code again.

options mprint mlogic symbolgen;

This writes diagnostic information to the LOG to help you debug the code. So please take a look at what is in the LOG to see if you can figure out what the problem is. If you still need help, show us the LOG from running this macro, all of it, 100%, nothing left out, every single character, not selected parts. Please copy the log as text and then paste it into the window that appears when you click on the </> icon. This preserves the formatting of the LOG and makes it easier to read ... some of us will not even try to read logs if you do not follow these instructions and preserve the formatting of the LOG.

Re: %do %to macro doenst stop after match a condition

erickdt — Wed, 14 Apr 2021 20:50:24 GMT

@PaigeMiller i've made identing, however when I use Crtl.c + cltr.v its lost... that is not my fault 😞

follow my log:

24 GOPTIONS ACCESSIBLE;
25
26 options mprint mlogic symbolgen;
27 %MACRO LOOP();
28 %MACRO X();
29 %MEND X;
30 DATA TESTE;
31 SET BASE_INFO_EXP2;
32 %DO DU=0 %TO 6;
33 %IF &DU. LE QT_PARCELA %THEN %DO;
34 %IF &DU. EQ 0 %THEN %DO;
35 RISCO_M&DU. = VL_PRODUCAO;
36 JUROS_M&DU. = 0;
37 AMORT_M&DU. = 0;
38 %END;
39 %ELSE %DO;
40 %LET DU_AUX = %SYSFUNC(SUM(&DU. -1));
41 JUROS_M&DU. = RISCO_M&DU_AUX. * (TAXA / 100);
42 AMORT_M&DU. = VL_PARCELA - JUROS_M&DU.;
43 RISCO_M&DU. = RISCO_M&DU_AUX. - AMORT_M&DU.;
44 %END;
45 %END;
46 %ELSE %DO;
47 JUROS_M&DU. = 0;
48 AMORT_M&DU. = 0;
49 RISCO_M&DU. = 0;
50 %END;
51 %END;
52 RUN;
53 %MEND;
2 The SAS System 14:22 Wednesday, April 14, 2021

54
55 %LOOP();
MLOGIC(LOOP): Beginning execution.
MPRINT(LOOP): DATA TESTE;
MPRINT(LOOP): SET BASE_INFO_EXP2;
MLOGIC(LOOP): %DO loop beginning; index variable DU; start value is 0; stop value is 6; by value is 1.
SYMBOLGEN: Macro variable DU resolves to 0
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 0
MLOGIC(LOOP): %IF condition &DU. EQ 0 is TRUE
SYMBOLGEN: Macro variable DU resolves to 0
MPRINT(LOOP): RISCO_M0 = VL_PRODUCAO;
SYMBOLGEN: Macro variable DU resolves to 0
MPRINT(LOOP): JUROS_M0 = 0;
SYMBOLGEN: Macro variable DU resolves to 0
MPRINT(LOOP): AMORT_M0 = 0;
MLOGIC(LOOP): %DO loop index variable DU is now 1; loop will iterate again.
SYMBOLGEN: Macro variable DU resolves to 1
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 1
MLOGIC(LOOP): %IF condition &DU. EQ 0 is FALSE
MLOGIC(LOOP): %LET (variable name is DU_AUX)
SYMBOLGEN: Macro variable DU resolves to 1
SYMBOLGEN: Macro variable DU resolves to 1
SYMBOLGEN: Macro variable DU_AUX resolves to 0
MPRINT(LOOP): JUROS_M1 = RISCO_M0 * (TAXA / 100);
SYMBOLGEN: Macro variable DU resolves to 1
SYMBOLGEN: Macro variable DU resolves to 1
MPRINT(LOOP): AMORT_M1 = VL_PARCELA - JUROS_M1;
SYMBOLGEN: Macro variable DU resolves to 1
SYMBOLGEN: Macro variable DU_AUX resolves to 0
SYMBOLGEN: Macro variable DU resolves to 1
MPRINT(LOOP): RISCO_M1 = RISCO_M0 - AMORT_M1;
MLOGIC(LOOP): %DO loop index variable DU is now 2; loop will iterate again.
SYMBOLGEN: Macro variable DU resolves to 2
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 2
MLOGIC(LOOP): %IF condition &DU. EQ 0 is FALSE
MLOGIC(LOOP): %LET (variable name is DU_AUX)
SYMBOLGEN: Macro variable DU resolves to 2
SYMBOLGEN: Macro variable DU resolves to 2
SYMBOLGEN: Macro variable DU_AUX resolves to 1
MPRINT(LOOP): JUROS_M2 = RISCO_M1 * (TAXA / 100);
SYMBOLGEN: Macro variable DU resolves to 2
SYMBOLGEN: Macro variable DU resolves to 2
MPRINT(LOOP): AMORT_M2 = VL_PARCELA - JUROS_M2;
SYMBOLGEN: Macro variable DU resolves to 2
SYMBOLGEN: Macro variable DU_AUX resolves to 1
SYMBOLGEN: Macro variable DU resolves to 2
MPRINT(LOOP): RISCO_M2 = RISCO_M1 - AMORT_M2;
MLOGIC(LOOP): %DO loop index variable DU is now 3; loop will iterate again.
SYMBOLGEN: Macro variable DU resolves to 3
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 3
MLOGIC(LOOP): %IF condition &DU. EQ 0 is FALSE
MLOGIC(LOOP): %LET (variable name is DU_AUX)
SYMBOLGEN: Macro variable DU resolves to 3
SYMBOLGEN: Macro variable DU resolves to 3
3 The SAS System 14:22 Wednesday, April 14, 2021

SYMBOLGEN: Macro variable DU_AUX resolves to 2
MPRINT(LOOP): JUROS_M3 = RISCO_M2 * (TAXA / 100);
SYMBOLGEN: Macro variable DU resolves to 3
SYMBOLGEN: Macro variable DU resolves to 3
MPRINT(LOOP): AMORT_M3 = VL_PARCELA - JUROS_M3;
SYMBOLGEN: Macro variable DU resolves to 3
SYMBOLGEN: Macro variable DU_AUX resolves to 2
SYMBOLGEN: Macro variable DU resolves to 3
MPRINT(LOOP): RISCO_M3 = RISCO_M2 - AMORT_M3;
MLOGIC(LOOP): %DO loop index variable DU is now 4; loop will iterate again.
SYMBOLGEN: Macro variable DU resolves to 4
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 4
MLOGIC(LOOP): %IF condition &DU. EQ 0 is FALSE
MLOGIC(LOOP): %LET (variable name is DU_AUX)
SYMBOLGEN: Macro variable DU resolves to 4
SYMBOLGEN: Macro variable DU resolves to 4
SYMBOLGEN: Macro variable DU_AUX resolves to 3
MPRINT(LOOP): JUROS_M4 = RISCO_M3 * (TAXA / 100);
SYMBOLGEN: Macro variable DU resolves to 4
SYMBOLGEN: Macro variable DU resolves to 4
MPRINT(LOOP): AMORT_M4 = VL_PARCELA - JUROS_M4;
SYMBOLGEN: Macro variable DU resolves to 4
SYMBOLGEN: Macro variable DU_AUX resolves to 3
SYMBOLGEN: Macro variable DU resolves to 4
MPRINT(LOOP): RISCO_M4 = RISCO_M3 - AMORT_M4;
MLOGIC(LOOP): %DO loop index variable DU is now 5; loop will iterate again.
SYMBOLGEN: Macro variable DU resolves to 5
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 5
MLOGIC(LOOP): %IF condition &DU. EQ 0 is FALSE
MLOGIC(LOOP): %LET (variable name is DU_AUX)
SYMBOLGEN: Macro variable DU resolves to 5
SYMBOLGEN: Macro variable DU resolves to 5
SYMBOLGEN: Macro variable DU_AUX resolves to 4
MPRINT(LOOP): JUROS_M5 = RISCO_M4 * (TAXA / 100);
SYMBOLGEN: Macro variable DU resolves to 5
SYMBOLGEN: Macro variable DU resolves to 5
MPRINT(LOOP): AMORT_M5 = VL_PARCELA - JUROS_M5;
SYMBOLGEN: Macro variable DU resolves to 5
SYMBOLGEN: Macro variable DU_AUX resolves to 4
SYMBOLGEN: Macro variable DU resolves to 5
MPRINT(LOOP): RISCO_M5 = RISCO_M4 - AMORT_M5;
MLOGIC(LOOP): %DO loop index variable DU is now 6; loop will iterate again.
SYMBOLGEN: Macro variable DU resolves to 6
MLOGIC(LOOP): %IF condition &DU. LE QT_PARCELA is TRUE
SYMBOLGEN: Macro variable DU resolves to 6
MLOGIC(LOOP): %IF condition &DU. EQ 0 is FALSE
MLOGIC(LOOP): %LET (variable name is DU_AUX)
SYMBOLGEN: Macro variable DU resolves to 6
SYMBOLGEN: Macro variable DU resolves to 6
SYMBOLGEN: Macro variable DU_AUX resolves to 5
MPRINT(LOOP): JUROS_M6 = RISCO_M5 * (TAXA / 100);
SYMBOLGEN: Macro variable DU resolves to 6
SYMBOLGEN: Macro variable DU resolves to 6
MPRINT(LOOP): AMORT_M6 = VL_PARCELA - JUROS_M6;
SYMBOLGEN: Macro variable DU resolves to 6
SYMBOLGEN: Macro variable DU_AUX resolves to 5
4 The SAS System 14:22 Wednesday, April 14, 2021

SYMBOLGEN: Macro variable DU resolves to 6
MPRINT(LOOP): RISCO_M6 = RISCO_M5 - AMORT_M6;
MLOGIC(LOOP): %DO loop index variable DU is now 7; loop will not iterate again.
MPRINT(LOOP): RUN;

NOTE: There were 213295 observations read from the data set WORK.BASE_INFO_EXP2.
NOTE: The data set WORK.TESTE has 213295 observations and 48 variables.
NOTE: Compressing data set WORK.TESTE decreased size by 53.15 percent.
Compressed is 1234 pages; un-compressed would require 2634 pages.
NOTE: DATA statement used (Total process time):
real time 2.02 seconds
user cpu time 1.60 seconds
system cpu time 0.13 seconds
memory 699.00k
OS Memory 26020.00k
Timestamp 04/14/2021 05:45:06 PM
Step Count 94 Switch Count 37
Page Faults 0
Page Reclaims 994
Page Swaps 0
Voluntary Context Switches 1423
Involuntary Context Switches 123
Block Input Operations 984
Block Output Operations 157984

MLOGIC(LOOP): Ending execution.
56
57
58 GOPTIONS NOACCESSIBLE;
59 %LET _CLIENTTASKLABEL=;
60 %LET _CLIENTPROCESSFLOWNAME=;
61 %LET _CLIENTPROJECTPATH=;
62 %LET _CLIENTPROJECTPATHHOST=;
63 %LET _CLIENTPROJECTNAME=;
64 %LET _SASPROGRAMFILE=;
65 %LET _SASPROGRAMFILEHOST=;
66
67 ;*';*";*/;quit;run;
68 ODS _ALL_ CLOSE;
69
70
71 QUIT; RUN;
72

Re: %do %to macro doenst stop after match a condition

ballardw — Wed, 14 Apr 2021 21:40:52 GMT

@erickdt wrote:

@PaigeMiller i've made identing, however when I use Crtl.c + cltr.v its lost... that is not my fault 😞

No it isn't your fault, it a by product of the forum. To prevent your text from getting reformatted you need to open a text box by clicking on the </> icon above the message window and the paste into that window. Which will look like:

data junk;
   set sashelp.class;
run;

If you want color highlighted code you could open a code box by clicking on the little "running man" icon to open a different box, and then paste.

None of the Macro %if comparisons ever see the value of your data step variables. Macro code is compiled and implemented before the data step executes.

When this is encountered

33 %IF &DU. LE QT_PARCELA %THEN %DO;

the macro variable &du is compared to the literal text QT_PARCELA, not the value of the variable in the data set that you likely intend. So the above is never true and the text of the code

RISCO_M&DU. = VL_PRODUCAO;
JUROS_M&DU. = 0;
AMORT_M&DU. = 0;

is not created for the data step.

Repeat: Macro %if does not see the value of variables in a data step.

Re: %do %to macro doenst stop after match a condition

PaigeMiller — Thu, 15 Apr 2021 10:53:34 GMT

Please follow the instructions I gave for preserving the formatting of the log.

Re: %do %to macro doenst stop after match a condition

Kurt_Bremser — Thu, 15 Apr 2021 11:30:03 GMT

Use this button for posting logs:

Use the "little running man" right next to it for SAS code, it mimics the coloring of the Enhanced Editor.

Don't shout at the SAS interpreter, code is much easier to read if not written in all uppercase.

Maxim 12: use indentation to identify code blocks, spaghetti code will only cause yourself (and others who have to work with your code) pain.

You are mixing up the data step with macro code, which never works. As others have mentioned, macro statements have no access to values present in the data step. Why? Because the macro processor is a preprocessor that prepares code, and does its work before the data step is even compiled.

You need to use arrays and the data step DO, no macro is needed at all.

Re: %do %to macro doenst stop after match a condition

Tom — Thu, 15 Apr 2021 13:39:43 GMT

This code makes no sense. The macro variable DU will take on the single digit strings 0 thru 6 . Any of those digits will always be larger than the letter Q. So your macro is generating this data step code to run:

MPRINT(LOOP):   DATA TESTE;
MPRINT(LOOP):   SET BASE_INFO_EXP2;
MPRINT(LOOP):   RISCO_M0 = VL_PRODUCAO;
MPRINT(LOOP):   JUROS_M0 = 0;
MPRINT(LOOP):   AMORT_M0 = 0;
MPRINT(LOOP):   JUROS_M1 = RISCO_M0 * (TAXA / 100);
MPRINT(LOOP):   AMORT_M1 = VL_PARCELA - JUROS_M1;
MPRINT(LOOP):   RISCO_M1 = RISCO_M0 - AMORT_M1;
MPRINT(LOOP):   JUROS_M2 = RISCO_M1 * (TAXA / 100);
MPRINT(LOOP):   AMORT_M2 = VL_PARCELA - JUROS_M2;
MPRINT(LOOP):   RISCO_M2 = RISCO_M1 - AMORT_M2;
MPRINT(LOOP):   JUROS_M3 = RISCO_M2 * (TAXA / 100);
MPRINT(LOOP):   AMORT_M3 = VL_PARCELA - JUROS_M3;
MPRINT(LOOP):   RISCO_M3 = RISCO_M2 - AMORT_M3;
MPRINT(LOOP):   JUROS_M4 = RISCO_M3 * (TAXA / 100);
MPRINT(LOOP):   AMORT_M4 = VL_PARCELA - JUROS_M4;
MPRINT(LOOP):   RISCO_M4 = RISCO_M3 - AMORT_M4;
MPRINT(LOOP):   JUROS_M5 = RISCO_M4 * (TAXA / 100);
MPRINT(LOOP):   AMORT_M5 = VL_PARCELA - JUROS_M5;
MPRINT(LOOP):   RISCO_M5 = RISCO_M4 - AMORT_M5;
MPRINT(LOOP):   JUROS_M6 = RISCO_M5 * (TAXA / 100);
MPRINT(LOOP):   AMORT_M6 = VL_PARCELA - JUROS_M6;
MPRINT(LOOP):   RISCO_M6 = RISCO_M5 - AMORT_M6;
MPRINT(LOOP):   RUN;

Which does not look very useful.

If you want the data step to dynamically decide which of a series of variables to change the value of then create an array and use a variable as the index into the array.

...
array juros_m [6];
array amort_m [6];
array risco_m [6];
...
/* 
JUROS_M&DU. = RISCO_M&DU_AUX. * (TAXA / 100);
AMORT_M&DU. = VL_PARCELA - JUROS_M&DU.;
RISCO_M&DU. = RISCO_M&DU_AUX. - AMORT_M&DU.
*/
juros_m[du] = risco_m[du_aux] * (taxa/100);
amort_m[du] = vl_parcela -  juros_m[du];
risco_m[du] = risco_m[du_aux] - amort-m[du];

Re: %do %to macro doenst stop after match a condition

erickdt — Thu, 15 Apr 2021 14:16:44 GMT

@Kurt_Bremser i've made the change to array, however could you help me to resolve the problem of the variable M_AUX?

M_AUX should be i - 1.

:'(

data teste;
SET TESTE2;
 risco_m0 = VL_PRODUCAO;
 array risco {6} risco_m1-risco_m6;
 do i = 1 to QT_PARCELA;
 	%LET M_AUX = %SYSFUNC(SUM(i,-1));
	 risco{i} = (RISCO_M&M_AUX. * (TAXA / 100));
 end;
run;

i get the follow error on log:

ERROR: Argument 1 to function SUM referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC 
or %QSYSFUNC function reference is terminated.

Re: %do %to macro doenst stop after match a condition

erickdt — Thu, 15 Apr 2021 14:38:18 GMT

thanks all in advanced.. follow the solution:

DATA TESTE;
SET TESTE2;
	 	RISCO_M0 = VL_PRODUCAO;
		JUROS_M0 = 0;
		AMORT_M0 = 0;
	 ARRAY JUROS {7} JUROS_M0-JUROS_M6;
	 ARRAY AMORT {7} AMORT_M0-AMORT_M6;
	 ARRAY RISCO {7} RISCO_M0-RISCO_M6;
		DO I = 2 TO QT_PARCELA+1;
			 	J = I -1;
					JUROS{I} =	(RISCO{J} * (TAXA / 100));
					AMORT{I} =	(VL_PARCELA - JUROS{I});
					RISCO{I} = 	 RISCO{J} 	- AMORT{I};

		END;
 DROP I J;
RUN;

Re: %do %to macro doenst stop after match a condition

Tom — Thu, 15 Apr 2021 15:16:54 GMT

So this is a nice simplified version of the same mistake that that original program had. You are trying to use macro code where you want actual SAS code. The macro code is evaluated before the data step starts running so you are running:

%LET M_AUX = %SYSFUNC(SUM(i,-1));

data teste;
SET TESTE2;
 risco_m0 = VL_PRODUCAO;
 array risco {6} risco_m1-risco_m6;
 do i = 1 to QT_PARCELA;
 	 risco{i} = (RISCO_M&M_AUX. * (TAXA / 100));
 end;
run;

And all you need to do is put the subtraction in the index into the array.

 do i = 1 to QT_PARCELA;
 	 risco{i} = (RISCO{i-1} * (TAXA / 100));
 end;

Now it is also easier to see another issue with this design. If the index iterates from 1 to N then the second index will iterate from 0 to N-1. So you might want to change the starting value.

do i = 2 to QT_PARCELA;

Or you can define the array to allow 0 as a valid index:.

 array risco {0:5} risco_m1-risco_m6;