First loop through columns and then loop through through rows

[Jass_Banga]

Hello,

I want to loop first through columns and then through rows.

data test ;
input cat $ cat3 cat4 cat5 cat6 cat7 cat8 num_rat ;
cards;
cat3 0 -1.78 -2.68 -3.06 -3.4 -3.83 1885
cat4 0 2.12 -2.15 -2.63 -2.94 -3.34 3151
cat5 0 2.45 1.16 -1.39 -1.99 -2.54 246
cat6 0 2.48 1.92 1.19 -1.13 -2.39 80
cat7 0 2.68 2.32 1.82 1.52 -1.56 89
;
run;

For example: I need to sum for cat3 (row) by first subtraction (0 + -1.78)*1885 + (-1.78 + -2.68)*1885 + (-2.68 + -3.06)*1885 + (-3.06 + -3.4)*1885 + (-3.4 + -3.83)*1885

and then calculate for all the rows (cat4, cat5, cat6, cat7) and then sum all the rows together and minimize them like (min(sum_rows).

[PaigeMiller]

In a data step

row_sum=(cat3+cat8+2*sum(of cat4-cat7))*num_rat;

To sum or find the minimum of all the values of row_sum, use PROC SUMMARY

proc summary data=whatever;
    var row_sum;
    output out=want sum=sum_sum_rows min=min_sum_rows;
run;

[Jass_Banga]

Thanks Paige, but I think this is not the required answer. I expect the do loop to run through each variable (columns) and calculate the function. Also I intend to minimize the function to one variable (lets say a z_score which can be said to (0 + -1.78)*1885*Z + (-1.78 + -2.68)*1885 *Z + (-2.68 + -3.06)*1885*Z + (-3.06 + -3.4)*1885*Z + (-3.4 + -3.83)*1885*Z.

Sorry I should have mentioned "Z" in my original post.

[ballardw]

Show the output exactly as you expect it to appear, including variable names.

It might also help to describe WHY and WHAT this calculation represents. Many times people take some example from a text book and don't realize that SAS has specialized functions or procedures to generate the result.

One might suspect that your 1885 value might represent an N of some sort but simple algebra reduces that to

(1885 *Z)*[(0 + -1.78)+(-1.78 + -2.68)+(-2.68 + -3.06)+(-3.06 + -3.4)+(-3.4 + -3.83)]

And anything that looks like

 (x+x1)+(x1+x2)+(x2+x3)+(x3+x4)
  = x+2x1+2x2+2x3+x4

So that can be considerably reduced. Which looks like that "loop" may not be needed at all.

Unless you mean a Z that changes, in which case you need to provide way more details.

If you are "standardizing" values you might look into proc STDIZE.

[Jass_Banga]

I understand Brad, and apologize for not being clear earlier.

Below is the equation I need Minimize:

where 

 and 

The matrix is data test ;

data test ;
input cat $ cat3 cat4 cat5 cat6 cat7 cat8 ;
cards;
cat3 0 -1.78 -2.68 -3.06 -3.4 -3.83
cat4 0 2.12 -2.15 -2.63 -2.94 -3.34
cat5 0 2.45 1.16 -1.39 -1.99 -2.54
cat6 0 2.48 1.92 1.19 -1.13 -2.39
cat7 0 2.68 2.32 1.82 1.52 -1.56
;
run;

%let rho = 0.8; *It will be changed after every run*;
%let z= 0.89; * It should be minimized*;

DATA TEST1;
SET test;
Array Cat_C(6) cat3-cat8;
Array Cat_g(5) catg3-catg7;
do i= 1to 5;
cat_g(i)= num_rat * ((((CDF('Normal', Cat_C(i+1))-CDF('NORMAL',Cat_C(i))-
((CDF('NORMAL', ((Cat_C(i+1) - (sqrt(&rho)*&z))/sqrt(1-&rho))))-(CDF ('NORMAL', ((Cat_C(i) - (sqrt(&rho)*&z))/sqrt(1-&rho))))))**2))/
(((CDF('NORMAL', ((Cat_C(i+1) - (sqrt(&rho)*&z))/sqrt(1-&rho))))-(CDF ('NORMAL', ((Cat_C(i) - (sqrt(&rho)*&z))/sqrt(1-&rho)))))*
(1-((CDF('NORMAL', ((Cat_C(i+1) - (sqrt(&rho)*&z))/sqrt(1-&rho))))-(CDF ('NORMAL', ((Cat_C(i) - (sqrt(&rho)*&z))/sqrt(1-&rho))))))));
end;
run;

where x(g+1) is the movement from cat3 to cat4. and rho needs to be any fixed number (say 0.8) and z needs to be minimized using any initial number (say 0.89). 

I hope it helps.

Thanks again.

[ballardw]

---

@Jass_Banga wrote:

I understand Brad, and apologize for not being clear earlier.

Below is the equation I need Minimize:

where 

 and 

The matrix is data test ;

data test ;
input cat $ cat3 cat4 cat5 cat6 cat7 cat8 ;
cards;
cat3 0 -1.78 -2.68 -3.06 -3.4 -3.83
cat4 0 2.12 -2.15 -2.63 -2.94 -3.34
cat5 0 2.45 1.16 -1.39 -1.99 -2.54
cat6 0 2.48 1.92 1.19 -1.13 -2.39
cat7 0 2.68 2.32 1.82 1.52 -1.56
;
run;

%let rho = 0.8; *It will be changed after every run*;
%let z= 0.89; * It should be minimized*;

DATA TEST1;
SET test;
Array Cat_C(6) cat3-cat8;
Array Cat_g(5) catg3-catg7;
do i= 1to 5;
cat_g(i)= num_rat * ((((CDF('Normal', Cat_C(i+1))-CDF('NORMAL',Cat_C(i))-
((CDF('NORMAL', ((Cat_C(i+1) - (sqrt(&rho)*&z))/sqrt(1-&rho))))-(CDF ('NORMAL', ((Cat_C(i) - (sqrt(&rho)*&z))/sqrt(1-&rho))))))**2))/
(((CDF('NORMAL', ((Cat_C(i+1) - (sqrt(&rho)*&z))/sqrt(1-&rho))))-(CDF ('NORMAL', ((Cat_C(i) - (sqrt(&rho)*&z))/sqrt(1-&rho)))))*
(1-((CDF('NORMAL', ((Cat_C(i+1) - (sqrt(&rho)*&z))/sqrt(1-&rho))))-(CDF ('NORMAL', ((Cat_C(i) - (sqrt(&rho)*&z))/sqrt(1-&rho))))))));
end;
run;

where x(g+1) is the movement from cat3 to cat4. and rho needs to be any fixed number (say 0.8) and z needs to be minimized using any initial number (say 0.89). 

I hope it helps.

Thanks again.

---

Can you provide a link to where that equation comes from? Minimizing a function like that sure smells related to some form of least squares regression.

[Jass_Banga]

https://www.z-riskengine.com/media/1032/a-one-parameter-representation-of-credit-risk-and-transition...

[PaigeMiller]

---

@Jass_Banga wrote:
Thanks Paige, but I think this is not the required answer. I expect the do loop to run through each variable (columns) and calculate the function. Also I intend to minimize the function to one variable (lets say a z_score which can be said to (0 + -1.78)*1885*Z + (-1.78 + -2.68)*1885 *Z + (-2.68 + -3.06)*1885*Z + (-3.06 + -3.4)*1885*Z + (-3.4 + -3.83)*1885*Z.

Sorry I should have mentioned "Z" in my original post.

---

There is no Z in your data set, however the way you have written the modification, you would simply use

row_sum=(cat3+cat8+2*sum(of cat4-cat7))*num_rat*z;

[Astounding]

Maybe somebody will come up a really complex way to do this in one step.  But I would use separate steps, first computing row totals:

data step1;
   set have;
   array col {3:8} cat3-cat8;
   row_total=0;
   do _n_=3 to 7;
      row_total + num_rat * (col{_n_} + col{_n_ + 1});
   end;
run;

Once you have the total totals completed in this fashion, the rest of the problem should be relatively easy.  (If it's not easy, you might have to explain more about what needs to happen at that point.)

[Ksharp]

Do you have IML or OR ?

@RobPratt  and @Rick_SAS  might give you a hand .

[RobPratt]

The following PROC OPTMODEL code matches your results for fixed Z = 0.89:

proc optmodel;
   set ROWS;
   num jmax = 6;
   set COLS = 1..jmax;
   num cat_c {ROWS, COLS};
   num num_rat {ROWS};
   read data test into ROWS=[_N_] {j in COLS} <cat_c[_N_,j]=col('cat'||j+2)> num_rat;
   print cat_c;

   var Z init 0.89;

   impvar P {i in ROWS, j in COLS diff {jmax}} = CDF('Normal', Cat_C[i,j+1]) - CDF('NORMAL', Cat_C[i,j]);
   impvar Phi {i in ROWS, j in COLS} = CDF('Normal', (Cat_C[i,j]-sqrt(&rho)*Z)/sqrt(1-&rho));
   impvar Delta {i in ROWS, j in COLS diff {jmax}} = Phi[i,j+1] - Phi[i,j];

   impvar ObjectiveSummand {i in ROWS, j in COLS diff {jmax}} = (num_rat[i] * (P[i,j] - Delta[i,j])**2 / (Delta[i,j] * (1 - Delta[i,j])));
   min Objective = sum {i in ROWS, j in COLS diff {jmax}} ObjectiveSummand[i,j];

   fix Z;
   solve;
   print Objective Z;
   print P Phi Delta;
   print ObjectiveSummand;
quit;

Uncomment the FIX statement to let the solver find Z.

[Jass_Banga]

Thanks Rob, you are a genius. your solution works nicely. 

I have one problem, if I don't have proc optmodel in my SAS Enterprise guide, what other options can be used to perform the same function?

Thanks & Regards,

Jass

[Jass_Banga]

Hello everyone,

Thank you for your code especially Rob. I do not have proc optmodel in my SAS subscription, but I have SAS IML. I have tried to write the code in IML but failed miserably. I tried to read from SAS documentation and online topics and have written the following code. I would be very thankful if you guys can help me fix it.

proc iml;
x={0 -1.78 -2.68 -3.06 -3.4 -3.83 1885,
0 2.12 -2.15 -2.63 -2.94 -3.34 3151,
0 2.45 1.16 -1.39 -1.99 -2.54 246,
0 2.48 1.92 1.19 -1.13 -2.39 80,
0 2.68 2.32 1.82 1.52 -1.56 89}
;
varNames= ("cat3" : "cat8" ||"num_rat");
rowNames= ("cat_3" : "cat_7");
n_x = nrow(x);
p_x = ncol(x-1);

print n_x p_x ;

create mydata1 from x[colname=varNames rowname=rowNames];
append from x [rowname = rowNames];
close mydata1;

start obj_z(p, phi, delta, rho, init_z);
num_col=ncol(x-1);
num_row=nrow(x);
num_r=x[,7];
do i = 1 to num_row;
p{i in num_rows, j in num_col} = CDF('Normal', x[i,j+1]) - CDF('NORMAL', x[i,j]);
phi{i in num_rows, j in num_col} = CDF('Normal', (Cat_C[i,j]-sqrt(&rho)*Z)/sqrt(1-&rho));
delta {i in num_rows, j in num_col} = Phi[i,j+1] - Phi[i,j];
sum_z= (num_rat[i] * (P[i,j] - Delta[i,j])**2 / (Delta[i,j] * (1 - Delta[i,j])));
finish;
call nlpqn(min(obj_z)) = "result";
print "result";
run;

[Jass_Banga]

Hello @RobPratt , @Ksharp 

I have written the following code but am getting error that 

ERROR: (execution) Matrix has not been set to a value

My code is given below:

proc iml;

/*start a function to minimized*/
start obj_z(y) ;
num_col=ncol(y);
num_row=nrow(y);
num_rat=y[,7];
do i = 1 to num_row;
do j= 1 to (num_col-2);
p[i,j] = CDF('Normal', y[i,j+1]) - CDF('NORMAL', y[i,j]);
phi[i,j] = CDF('Normal', (y[i,j]-sqrt(rho)*Z)/sqrt(1-rho));
Delta [i,j] = Ph([i,j+1] - Phi[i,j]);
sum_z= sum((num_r (i)* (P[i,j] - Delta[i,j])**2) / (Delta[i,j] * (1 - Delta[i,j])));

return (sum_z);
end;
end;
finish obj_z ;

x_1={0 -1.78 -2.68 -3.06 -3.4 -3.83 1885,
0 2.12 -2.15 -2.63 -2.94 -3.34 3151,
0 2.45 1.16 -1.39 -1.99 -2.54 246,
0 2.48 1.92 1.19 -1.13 -2.39 80,
0 2.68 2.32 1.82 1.52 -1.56 89}
;
num_row_1 = nrow(x_1);
num_col_1 = ncol(x_1);

/*print n_x p_x ;*/
varNames= ("cat3" : "cat8" || "num_rat");
rowNames= ("cat3" : "cat7");

create y from x_1[colname=varNames rowname=rowNames];
append from x_1 [rowname = rowNames];
/*close x_1;*/

start_z= {0.89}; /*initialize z*/
rho={0.8}; /* rho is a constant*/
OPTN= {0 2}; /* options for minimimization problem and result output*/
call NLPTR(xres, rc, "obj_z",start_z , OPTN) ; /* calling the function*/
/*print "result";*/
Quit;

[Ksharp]

Delta [i,j] = Ph([i,j+1] - Phi[i,j]);

In IML, don't have function Ph( ) .