topic Re: Can proc iml be converted into an sql format in SAS Programming

Can proc iml be converted into an sql format

Tzar — Wed, 31 Jul 2019 12:50:19 GMT

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;

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

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;

Re: Can proc iml be converted into an sql format

PaigeMiller — Wed, 31 Jul 2019 13:02:20 GMT

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

Re: Can proc iml be converted into an sql format

Reeza — Wed, 31 Jul 2019 15:22:45 GMT

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.

Re: Can proc iml be converted into an sql format

Tom — Wed, 31 Jul 2019 15:35:12 GMT

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.

Re: Can proc iml be converted into an sql format

Ksharp — Thu, 01 Aug 2019 12:23:04 GMT

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 .