topic Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel? in SAS/IML Software and Matrix Computations

How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

hamed_jabarian — Tue, 23 Dec 2014 17:59:47 GMT

I am striving to calculate Value at risk in financial market, in order to produce 10% VaR in excel it is easy to use Norm.inv(probability,mean,std). what is the same code in IML?

Furthermore in excel we have " small(array,3) " to calculate 3rd smallest value. how is it done in IML?

Please help me in this topic. thanks in advance..

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

Rick_SAS — Wed, 24 Dec 2014 12:54:20 GMT

The inverse CDF function is called the QUANTILE function in SAS. To compute the inverse CDF of the normal distribution, use the following:

proc iml;

prob=0.5; mean=0; std=1;

q = quantile("Normal", prob, mean, std);

For computing the kth smallest, I'd use the RANK function., which returns the order of each element in a sorted list. You can use the LOC function to find the index that contains the kth smallest, and then extract the value, as follows:

x = 100:91;

r = rank(x);

idx = loc(r=3); /* location of the 3rd smallest */

small3 = x[idx]; /* value of the 3rd smallest */

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

Ksharp — Wed, 24 Dec 2014 13:11:58 GMT

Rick,

What if there are some ties ? How to get rid of them ?

proc iml;
m = { 1 2 0,
      2 4 0,
      10 11 12,
      2 2 2};
r=rank(m);
x=loc(r=3); 
smallest3=m;
print smallest3;
quit;

Xia Keshan

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

hamed_jabarian — Wed, 24 Dec 2014 14:01:26 GMT

Real Thanks

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

Rick_SAS — Fri, 26 Dec 2014 12:38:11 GMT

Why would I want to get rid of them? The algorithm returns the observations with the k_th smallest values. If you just want one, you can use

smallest3=m[1];

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

Ksharp — Fri, 26 Dec 2014 13:48:32 GMT

As you can see the code return 1,but the smallest 3 is 2 . so do I get that 2 ?

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

Rick_SAS — Fri, 26 Dec 2014 18:10:29 GMT

Sorry, I didn't understand your question previously. In your example you have two 0s. Your code returns 1 because that is the third value in a ranked list of the values: 0, 0, 1, 2, 2, 2,....

If you want to return all values that have the third largest UNIQUE value (which is 2), then you can use the UNIQUE-LOC technique: The UNIQUE-LOC trick: A real treat! - The DO Loop

The code would look like this:

m = { 1 2 0,

2 4 0,

10 11 12,

2 2 2};

u = unique(m); /* unique values */

k = 3; /* look for the k_th smallest */

idx = loc(m=u); /* find observations */

print (idx`)[L="Obs Num"] (m[idx])[L="Value"];

Re: How can i produce syntax in IML9.2 equivalent to " NORM.INV " in excel?

Ksharp — Sat, 27 Dec 2014 08:18:17 GMT

Well done. Thanks Rick .