Macro errors

[JovanaUniCredit]

I am trying to execute this code/macro, but there is some error:

%macro sqlloop(start,end,step,pocetak,kraj);
PROC SQL;
%DO i=&start. %TO &end. %BY &step.;
%DO j=&pocetak. %TO &kraj.;
%makro(i,j);
%END;
%END;
QUIT;
%mend;

%sqlloop(start=0, end=0.2507245866,step = 0.001,pocetak=1, kraj=242)

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&end.
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&step.
ERROR: The %BY value of the %DO I loop is invalid.
ERROR: The macro SQLLOOP will stop executing.

Is it possible fro macro to work with decimal numbers? Or something else is problem?

[JovanaUniCredit]

...And what is proposal hot to join all tables made in macro? Since,  those tables contain just one row, I want them one below another in one table.

[andreas_lds]

---

@JovanaUniCredit wrote:

...And what is proposal hot to join all tables made in macro? Since,  those tables contain just one row, I want them one below another in one table.

---

Please explain what you are trying to achieve. I don't think that the loops are necessary at all - at least not as macro-code.

[JovanaUniCredit]

The export from my macro should be more than 200 tables with just one row. I want to join them, with all rows in one table one below another.

I did this like this:

PROC SQL;
CREATE TABLE bete as
SELECT * FROM CTAge_obs1
UNION SELECT * FROM CTAge_obs2
UNION SELECT * FROM CTAge_obs3
UNION SELECT * FROM CTAge_obs4
UNION SELECT * FROM CTAge_obs5
UNION SELECT * FROM CTAge_obs6
UNION SELECT * FROM CTAge_obs7
UNION SELECT * FROM CTAge_obs8
UNION SELECT * FROM CTAge_obs9
UNION SELECT * FROM CTAge_obs10

...

until the last table.

Can it be done on more sophisticated way? Not to write it more than 200 times?

[Kurt_Bremser · Accepted solution]

That is MUCH easier accomplished with a dead-simple data step and NO macro code AT ALL:

data bete;
set ctage_obs:;
run;

or

data bete;
set ctage_obs1-ctage_obs200;
run;

---

@JovanaUniCredit wrote:

The export from my macro should be more than 200 tables with just one row. I want to join them, with all rows in one table one below another.

I did this like this:

PROC SQL;
CREATE TABLE bete as
SELECT * FROM CTAge_obs1
UNION SELECT * FROM CTAge_obs2
UNION SELECT * FROM CTAge_obs3
UNION SELECT * FROM CTAge_obs4
UNION SELECT * FROM CTAge_obs5
UNION SELECT * FROM CTAge_obs6
UNION SELECT * FROM CTAge_obs7
UNION SELECT * FROM CTAge_obs8
UNION SELECT * FROM CTAge_obs9
UNION SELECT * FROM CTAge_obs10

...

until the last table.

Can it be done on more sophisticated way? Not to write it more than 200 times?

---

[JovanaUniCredit]

Thank you, this helps a lot!

A what about my first macro?

I wrote this :
%macro makro(korak,broj);
DATA CTAge;
SET obs_stg_change;
step=((1-alpha)/log(50))/(50-1);
y= alpha+ &korak*log(Age);
IF(I1=1) THEN min_Threshold = min(y,PDThreshold);
IF(I2=1) THEN max_Threshold = max(y,PDThreshold);
min_max_Threshold=COALESCE(min_Threshold, max_Threshold);
IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;
RUN;

/*0.5757232999 SUM SQUARE*/

PROC SQL;
CREATE TABLE CTAge_obs&broj AS
SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,
(SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */
/* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/
/* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and new one */
(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/
SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */
ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */
ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee
FROM WORK.CTAge t1;
QUIT;
%mend;
%makro(0,1);
%makro(0.001,2);
%makro(0.0011,3);
%makro(0.0012,4);
%makro(0.0013,5);
%makro(0.0014,6);
...

just like the statement from previous my comment, more that 200 times- that is the number of my steps, that I have to calculate, and I wanted to shorten it with:
%macro sqlloop(start,end,step,pocetak,kraj);
PROC SQL;
%DO i=&start. %TO &end. %BY &step.;
%DO j=&pocetak. %TO &kraj.;
%makro(i,j);
%END;
%END;
QUIT;
%mend;

%sqlloop(start=0, end=0.2507245866,step = 0.001,pocetak=1, kraj=242)

But it doesn't work. The error was:
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&end.
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&step.
ERROR: The %BY value of the %DO I loop is invalid.
ERROR: The macro SQLLOOP will stop executing.

So, I concluded that there is problem with decimal numbers in macro.

Am I right?

[Kurt_Bremser]

PLEASE (and I mean that!) use the proper subwindows for posting code and logs, as already demanded by @PaigeMiller . It is NOT rocket science, and it won't make your head explode. Promised.

Use the </> button for logs and other structured text:

and use the "little running man" right next to it for code.

Your macro can easily be translated to data step code:

data _null_;
do i = 0 to 0.2507245866 by .001;
  do  j = 1 to 242;
    call execute(cats('%nrstr(%makro(',i,',',j,'))'));
  end;
end;
run;

Note that your do loop will not result in the values you posted, you'll get

0
.001
.002
.003

instead

[JovanaUniCredit]

1                                                          The SAS System                        Tuesday, April 28, 2020 12:45:00 PM

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='\\srvnas\common\CRO\Strategic Risk\Credit risk Governance and Models\Credit Risk Modeling\IFRS
5        ! 9\03 IFRS 9 Modeling\02 IFRS 9 TL modeling\QE & TL 2020\Transfer logic\IFRS 9 2020 TL v8.egp';
6          %LET _CLIENTPROJECTPATHHOST='UCB29756';
7          %LET _CLIENTPROJECTNAME='IFRS 9 2020 TL v8.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to "F:\WORK\_TD4820_SRVSAS06_\Prc2/"
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         options mprint symbolgen mlogic;
27         options compress=binary;
28         
29         
30         
31         DATA QntRegTest;
32         SET QntRegCorp /*QntRegCorpNonRev*/ /*QntRegCorpREV*/;
33         
34         /*ISTA JE PODELA KOD SVA 3 segmenta*/
35         IF RatingClassFirstAv in (1,2,3,4,5) THEN I1 = 1;
36         ELSE I1 = 0;
37         IF RatingClassFirstAv in (6,7,8) THEN I2 = 1;
38         ELSE I2 = 0;
39         
40         LnAge1 = LnAge*I1;
41         LnAge2 = LnAge*I2;
42         
43         RUN;

NOTE: There were 26168 observations read from the data set WORK.QNTREGCORP.
NOTE: The data set WORK.QNTREGTEST has 26168 observations and 79 variables.
NOTE: Compressing data set WORK.QNTREGTEST decreased size by 83.41 percent. 
      Compressed is 106 pages; un-compressed would require 639 pages.
NOTE: DATA statement used (Total process time):
      real time           0.25 seconds
      cpu time            0.25 seconds
      

44         
45         PROC QUANTREG DATA=QntRegTest OUTest=QntRegEst algorithm=interior (tolerance=1e-5) ci=resampling;
2                                                          The SAS System                        Tuesday, April 28, 2020 12:45:00 PM

46         	MODELLnAge12WORM: MODEL AP1 = LnPD0 LnAge1 LnAge2 / quantile=0.9 /*plots=all*/;
47         	/*testAll: test LnPD0 LnAge1 LnAge2/ wald lr*/;
48         	OUTPUT OUT=QntRegLnAge12 p=predicted;
49         	title 'Quantile regression';
50         RUN;

NOTE: The data set WORK.QNTREGEST has 1 observations and 10 variables.
NOTE: Compressing data set WORK.QNTREGEST increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: The data set WORK.QNTREGLNAGE12 has 26168 observations and 81 variables.
NOTE: Compressing data set WORK.QNTREGLNAGE12 decreased size by 82.79 percent. 
      Compressed is 110 pages; un-compressed would require 639 pages.
NOTE: PROCEDURE QUANTREG used (Total process time):
      real time           1.15 seconds
      cpu time            1.15 seconds
      

51         
52         DATA check;
53         SET QntRegLnAge12;
54         IF AP1 > predicted then Stage = 2; else Stage = 1;
55         dummy = 1;
56         RUN;

NOTE: There were 26168 observations read from the data set WORK.QNTREGLNAGE12.
NOTE: The data set WORK.CHECK has 26168 observations and 83 variables.
NOTE: Compressing data set WORK.CHECK decreased size by 83.05 percent. 
      Compressed is 111 pages; un-compressed would require 655 pages.
NOTE: DATA statement used (Total process time):
      real time           0.25 seconds
      cpu time            0.25 seconds
      

57         
58         DATA QRParameters;
59         SET check;
60         
61         Intercept = -0.6436; /*PROMENA*/
62         Coeff_LnPD0 = -0.2536; /*PROMENA*/
63         Coeff_LnAge1 = 0.6548; /*PROMENA*/
64         Coeff_LnAge2 = 0.2851; /*PROMENA*/
65         ALPHA =
65       ! SQRT((exp(Intercept+Coeff_LnPD0*log(0.005985769))*0.005985769)*(exp(Intercept+Coeff_LnPD0*log(0.023412558))*0.023412558))
65       ! ;
66         RUN;

NOTE: There were 26168 observations read from the data set WORK.CHECK.
NOTE: The data set WORK.QRPARAMETERS has 26168 observations and 88 variables.
NOTE: Compressing data set WORK.QRPARAMETERS decreased size by 81.53 percent. 
      Compressed is 121 pages; un-compressed would require 655 pages.
NOTE: DATA statement used (Total process time):
      real time           0.26 seconds
      cpu time            0.26 seconds
      

67         
68         DATA beta_min_max;
69         SET QRParameters;
3                                                          The SAS System                        Tuesday, April 28, 2020 12:45:00 PM

70         
71         IF (Coeff_LnAge1 > 0 AND Coeff_LnAge2 > 0) THEN DO;
72         MIN = 0;
73         MAX = (1- ALPHA)/LOG(50);
74         END;
75         ELSE IF (Coeff_LnAge1 < 0 AND Coeff_LnAge2 < 0) THEN MAX = 0 AND MIN =  -1*(ALPHA)/LOG(50);
76         RUN;

NOTE: There were 26168 observations read from the data set WORK.QRPARAMETERS.
NOTE: The data set WORK.BETA_MIN_MAX has 26168 observations and 90 variables.
NOTE: Compressing data set WORK.BETA_MIN_MAX decreased size by 81.38 percent. 
      Compressed is 119 pages; un-compressed would require 639 pages.
NOTE: DATA statement used (Total process time):
      real time           0.26 seconds
      cpu time            0.26 seconds
      

77         
78         DATA realized_thresholds/*realized_thresholds_RCG1 realized_thresholds_RCG2*/;
79         SET beta_min_max  (KEEP= LnPD0 PDPuncFirstAv RatingClassFirstAV RatingFirstAV RatingCurr RefDate Age ClientID ClientName
79       ! TransID SegmentFinal I1 I2 predicted PDPuncFirstAv PDPuncCurr Intercept Coeff_LnPD0 Coeff_LnAge1 Coeff_LnAge2 ALPHA MIN
79       ! MAX LnAge1 LnAge2);
80         
81         PDThreshold = EXP(predicted)*PDPuncFirstAv; /* Predicted PD at reporting date (i.e. PD threshold)*/
82         IF PDThreshold >= 0.99 THEN PDThreshold = 0.99;
83         /*PDPuncCurr- OBSERVED PD at reporting date*/
84         IF (PDPuncCurr > PDThreshold) THEN StgChange = 1; /* boolean vector which indicates stage 2 obs. - 1's are in stage 2,
84       ! 0's in stage 1 */
85         ELSE StgChange = 0;
86         
87         *IF I1 = 1 THEN OUTPUT realized_thresholds_RCG1;
88         *ELSE OUTPUT realized_thresholds_RCG2;
89         RUN;

NOTE: There were 26168 observations read from the data set WORK.BETA_MIN_MAX.
NOTE: The data set WORK.REALIZED_THRESHOLDS has 26168 observations and 26 variables.
NOTE: Compressing data set WORK.REALIZED_THRESHOLDS decreased size by 89.51 percent. 
      Compressed is 67 pages; un-compressed would require 639 pages.
NOTE: DATA statement used (Total process time):
      real time           0.18 seconds
      cpu time            0.17 seconds
      

90         
91         
92         PROC SQL;
93         CREATE TABLE obs_stg_change AS
94         SELECT t1.*, sum(t1.StgChange) as sum_StgChange,
95         	count(t1.TransID) as no_obs,
96         	(SUM(t1.StgChange))/count(t1.TransID) as QUANTILE,
97             (1-(SUM(t1.StgChange))/count(t1.TransID)) AS quantile_or
98         
99         FROM  realized_thresholds /*realized_thresholds_RCG1 realized_thresholds_RCG2*/ t1;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compressing data set WORK.OBS_STG_CHANGE decreased size by 88.55 percent. 
      Compressed is 75 pages; un-compressed would require 655 pages.
NOTE: Table WORK.OBS_STG_CHANGE created, with 26168 rows and 30 columns.

4                                                          The SAS System                        Tuesday, April 28, 2020 12:45:00 PM

100        
101        QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.21 seconds
      cpu time            0.21 seconds
      

102        
103        
104        %macro makro(korak,broj);
105        DATA CTAge;
106        SET obs_stg_change;
107        step=((1-alpha)/log(50))/(50-1);
108        y= alpha+ &korak*log(Age);
109        IF(I1=1) THEN min_Threshold = min(y,PDThreshold);
110        IF(I2=1) THEN max_Threshold = max(y,PDThreshold);
111        min_max_Threshold=COALESCE(min_Threshold, max_Threshold);
112        IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;
113        RUN;
114        
115        /*0.5757232999 SUM SQUARE*/
116        
117        PROC SQL;
118           CREATE TABLE CTAge_obs&broj AS
119           SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,
120                    (SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */
121        /*			(SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/
122         /*          (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/	/*Calculate distance between old CT and
122      ! new one */
123        			(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/
124        			SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */
125        			ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */
126        			ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee
127              FROM WORK.CTAge t1;
128        QUIT;
129        %mend;
130        
131        %macro sqlloop(start,end,step,pocetak,kraj);
132           PROC SQL;
133             %DO i=&start. %TO &end. %BY &step.;
134        	%DO j=&pocetak. %TO &kraj.;
135               %makro(i,j);
136        	   %END;
137             %END;
138           QUIT;
139        %mend;
140        
141        GOPTIONS NOACCESSIBLE;
142        %LET _CLIENTTASKLABEL=;
143        %LET _CLIENTPROCESSFLOWNAME=;
144        %LET _CLIENTPROJECTPATH=;
145        %LET _CLIENTPROJECTPATHHOST=;
146        %LET _CLIENTPROJECTNAME=;
147        %LET _SASPROGRAMFILE=;
148        %LET _SASPROGRAMFILEHOST=;
149        
150        ;*';*";*/;quit;run;
151        ODS _ALL_ CLOSE;
5                                                          The SAS System                        Tuesday, April 28, 2020 12:45:00 PM

152        
153        
154        QUIT; RUN;
155        

[Kurt_Bremser]

Replace the sqlloop macro with the data step I posted earlier; you are also having a conceptual mistake in the sqlloop macro. In the macro, you try to wrap the other macro inside a SQL step. Since that macro contains a data step, this will immediately the SQL step on the "outside".

The data _null_ step does not have that problem, as it just feeds code into the execution queue for later.

[JovanaUniCredit]

Also, when I run the proposed solution, it looks like it is never ending loop.

[Kurt_Bremser]

---

@JovanaUniCredit wrote:

Also, when I run the proposed solution, it looks like it is never ending loop.

---

I am not surprised. This

do i = 0 to 0.2507245866 by .001;

will run 251 iterations. Multiply that by the 242 steps of the inner loop, you'll get 60742 executions of the macro %makro. If that only needs 1 second for each run, you'll still be waiting for a day.

I guess you go back to square one and contemplate what you actually want to do.

[JovanaUniCredit]

Actually, the idea behind this was to add j in order to have better naming for every table, so as to know which table is for which step and how to join them. 

Do You have any suggestion how to change it?

The code that I wrote is:

%macro makro(korak,broj);
DATA CTAge;
SET obs_stg_change;
step=((1-alpha)/log(50))/(50-1);
y= alpha+ &korak*log(Age);
IF(I1=1) THEN min_Threshold = min(y,PDThreshold);
IF(I2=1) THEN max_Threshold = max(y,PDThreshold);
min_max_Threshold=COALESCE(min_Threshold, max_Threshold);
IF PDPuncCurr > min_max_Threshold THEN StgChange_CT = 1; ELSE StgChange_CT = 0;
RUN;

/*0.5757232999 SUM SQUARE*/

PROC SQL;
CREATE TABLE CTAge_obs&broj AS
SELECT &korak AS step, min(t1.sum_StgChange) as sum_StgChange,
(SUM(t1.StgChange_CT)) AS SUM_StgChange_CT, /*a1*//* count # of obs which change stage */
/* (SUM(t1.StgChange_CT) - t1.sum_StgChange) AS PROVERA,*/
/* (SUM(abs(t1.StgChange_CT-t1.StgChange))) AS SUM_StgChange_DIFF,*/ /*Calculate distance between old CT and new one */
(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS SSQ2, /*sum of square*/
SQRT(SUM((t1.min_max_Threshold-t1.PDThreshold)**2)) AS dist_prov, /* square root of the distance */
ABS(SUM(t1.StgChange_CT) - min(t1.sum_StgChange)) AS err_stage2, /* Sum of obs which change stage */
ABS(SUM(t1.StgChange_CT)-sum(t1.StgChange))/MIN(t1.no_obs) AS ee
FROM WORK.CTAge t1;
QUIT;
%mend;

%makro(0,1);
%makro(0.001,2);
%makro(0.0011,3);
%makro(0.0012,4);
%makro(0.0013,5);
%makro(0.0014,6);
%makro(0.0015,7);
%makro(0.0016,8);
%makro(0.0017,9);
%makro(0.0018,10);
%makro(0.0019,11);
%makro(0.002,12);
%makro(0.0021,13);
%makro(0.0022,14);
%makro(0.0023,15);
%makro(0.0024,16);
%makro(0.0025,17);
%makro(0.0026,18);
%makro(0.0027,19);

...

and so on until the 242. table and:

data bete;
set ctage_obs:;
run;

Do You have any suggestion what would be the best solution instead of writting %makro(x,y); for 242 times?

Thanks a lot!

[Kurt_Bremser]

So you only need one loop:

data _null_;
call execute('%nrstr(%makro(0,1))');
i = .001;
do j = 2 to 242;
  call execute(cats('%nrstr(%makro(',i,',',j,'))'));
  i = i + .0001;
end;
run;

[JovanaUniCredit]

Again, this is not what I wanted, because when I run it I can see that for one I value there is j different tables and I don't want that.

I just want that every I value has it's index. I have 242 different I values so I need 242 different coefficients for created tables, for every I. Whenever I value is changes, also j values has to be changed to next integer. So:

i            j

0          1

0.001   2

0.0011 3

0.0012 4

and so on.

Any suggestion?

[Tom]

I don't see a pattern here.  Do you know the list of I values in advance?  Put them into a dataset. Or a delimited list in a single macro variable.

%let ilist= 0  0.001 0.0011 0.0012  ;
%do j=1 %to %sysfunc(countw(&ilist,%str( )));
  %let i=%scan(&ilist,&j);
   ...
%end;