topic Re: First loop through columns and then loop through through rows in SAS Programming

First loop through columns and then loop through through rows

Jass_Banga — Fri, 04 Sep 2020 17:13:43 GMT

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).

Re: First loop through columns and then loop through through rows

PaigeMiller — Fri, 04 Sep 2020 17:33:43 GMT

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;

Re: First loop through columns and then loop through through rows

Jass_Banga — Fri, 04 Sep 2020 18:15:58 GMT

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.

Re: First loop through columns and then loop through through rows

ballardw — Fri, 04 Sep 2020 19:22:57 GMT

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.

Re: First loop through columns and then loop through through rows

Jass_Banga — Fri, 04 Sep 2020 19:35:35 GMT

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.

Re: First loop through columns and then loop through through rows

Astounding — Fri, 04 Sep 2020 19:40:31 GMT

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.)

Re: First loop through columns and then loop through through rows

PaigeMiller — Fri, 04 Sep 2020 19:54:33 GMT

@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;

Re: First loop through columns and then loop through through rows

ballardw — Fri, 04 Sep 2020 20:46:50 GMT

@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.

Re: First loop through columns and then loop through through rows

Jass_Banga — Fri, 04 Sep 2020 20:53:34 GMT

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

Re: First loop through columns and then loop through through rows

Ksharp — Sat, 05 Sep 2020 12:13:12 GMT

Do you have IML or OR ?

@RobPratt and @Rick_SAS might give you a hand .

Re: First loop through columns and then loop through through rows

RobPratt — Tue, 08 Sep 2020 22:41:43 GMT

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.

Re: First loop through columns and then loop through through rows

Jass_Banga — Wed, 09 Sep 2020 00:12:21 GMT

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

Re: First loop through columns and then loop through through rows

Jass_Banga — Wed, 09 Sep 2020 00:30:47 GMT

I have IML.

Re: First loop through columns and then loop through through rows

Ksharp — Wed, 09 Sep 2020 11:39:30 GMT

Try free version SAS.

https://welcome.oda.sas.com/login

Re: First loop through columns and then loop through through rows

Jass_Banga — Wed, 09 Sep 2020 12:05:54 GMT

Thanks Ksharp. My problem is that if most of my group friends also don't have Proc Optmodel, thats why I would be very thankful if you guys can help me with the code in SAS IML.

Regards,

Jass

Re: First loop through columns and then loop through through rows

Ksharp — Wed, 09 Sep 2020 12:11:48 GMT

Sorry. I have no time. Maybe @Rick_SAS could write it for you .

Re: First loop through columns and then loop through through rows

Jass_Banga — Fri, 11 Sep 2020 18:53:43 GMT

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;

Re: First loop through columns and then loop through through rows

Jass_Banga — Tue, 15 Sep 2020 19:08:10 GMT

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;

Re: First loop through columns and then loop through through rows

Ksharp — Wed, 16 Sep 2020 11:24:39 GMT

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

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

Re: First loop through columns and then loop through through rows

Jass_Banga — Wed, 16 Sep 2020 18:45:05 GMT

Thanks @Ksharp , now the error is

ERROR: (execution) Invalid subscript or subscript out of range.

Additionally, how can change the IML code to caculate z value for each observation (more like the IML equivalent of "diff" command in OPTMODEL?