Can proc iml be converted into an sql format

[Tzar]

Hi. I have some code in the proc iml format and was wondering if it could possibly be converted into a sql format?

proc iml;

/****************************************

     Reading the data into a iml matrix

*****************************************/

Use price3_1;

/*price = j(2000000,482,0);*/ /*(number of transactions, number of date points,fill with zeros) = creating the size of the matrix price3  */

read all into price;

/************************************************************

     Creating the X and Y matrix

Y is a matrix containing all the data for the

first month of the entire time period (bases time step)

and its size one by (number of transactions)

X is a matrix containing the data point from the second

time period to the last time period of all the transactions,

and its size is  (time steps -1) by (amount of transactions)

*************************************************************/

X = price[,2:ncol(price)];

Y = Price[,1];

/*************************************************************

     In order to compute beta according to the Case Shiller

     method You have to change the first price in each row

     of the X matrix to a negative value and keep the second

     price the same, except if the transactions first price

     falls within the bases time period thus in the Y matrix    

     then the first and only price in the X matrix row stays

     positive.

**************************************************************/

do i = 1 to nrow(x);

one = 0;

     if y[i,1] > 0 then

     do j = 1 to ncol(x);

           x[i,j] = x[i,j];

     end;

     else

     do j = 1 to ncol(x);

           if one = 0

           then x[i,j] = x[i,j]*-1 ;

           else x[i,j] = x[i,j];

           one = x[i,j]+one;

     end;

end;

/*************************************************************

     Z is basically the X matrix except that the price is

     changed to one thus where there is a negative price in

     X   there is -1 in Z and where there is a positive price

     in X there is a 1 in Z else the rest stays zero.

**************************************************************/

Z = j(nrow(x),ncol(x),1);

do i = 1 to nrow(x);

     do j = 1 to ncol(x);

           if x[i,j] = 0 then z[i,j] = 0;

           if x[i,j] > 0 then z[i,j] = 1;

           if x[i,j] < 0 then z[i,j] = -1; 

     end;

end;

[PaigeMiller]

---

@Tzar wrote:

Hi. I have some code in the proc iml format and was wondering if it could possibly be converted into a sql format?

---

Having examined this IML code, where you are performing operations across the rows according to some value of another variable in the row, I am going to answer your question with NO. (or it would be extremely difficult to do in SQL) (it might be also easily do-able in a DATA step using ARRAYs)

There's a maxim about this. Use the right tool for the job, which appears to be IML (or maybe a DATA step)

[Reeza]

It's doable but painful and long. SQL doesn't have the concept of arrays/columns so anything that loops across multiple columns will have to instead explicitly list out all the columns or you'd have to change your data structure to accomplish this. It's possible but not easy.

[Tom]

What did you try?

First thing is to convert your "matrix" into a proper dataset. If you have a 2 dimensional matrix with R rows and C cols you should store that as a dataset with R times C observations.  One variable has the row index, one has the column index and the third has the value.   

proc iml;
price = 
{10 20 30
,40 50 60
};
print price;
quit;
 
data price_table;
  row+1;
  do col=1 to 3 ;
    input price @;
    output;
  end;
cards;
10 20 30
40 50 60
;

proc print;
run;

            price

       10        20        30
       40        50        60


Obs    row    col    price

 1      1      1       10
 2      1      2       20
 3      1      3       30
 4      2      1       40
 5      2      2       50
 6      2      3       60

Similarly a 3 dimensional array becomes a dataset with 4 variables.

[Ksharp]

IML is not right tool for your purpose .

Try Data Step .

And post an example include INPUT and OUTPUT .

Somebody would give you right data step code .