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;
@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)
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.
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 .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.