turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-11-2016 10:33 AM

I'm perhaps over-complicating this problem, and for various reasons I'm keen to keep this in IML as it is part of an iterative process which requires matrix calculations later on. I wondered if there was some way I could use the LOC function but thinking probably not.

**High level description of problem**

I have a list of percentages. The percentages determine the rate you receive. I want to re-code the original percentages in one vector into the target rates in another vector.

**Detail**

I wish to re-code each element of **Vector A **into a value in **Vector C. **

**Vector B** lets us determine the column of Vector C we want to return. So, any element with value less than or equal to 0.5 falls into category 1 (i.e. that is returns 0.02 from Vector C); between 0.51 and 0.55 (inclusive) would be category 2, and so on.

**Example**

So, in this example, element 1 of Vector A is 0.78 - it falls into category 7 (determined by vector B). Therefore, element 1 of the resulting vector will be 0.08 (which matches column **7** of vector C).

**Vector A** contains the percentages which I want to re-code.

M1 | M2 | M3 | M4 |

0.78 | 0.75 | 0.72 | 0.69 |

**Vector B** contains contains the categories (these allow me to determine which column of vector C i need).

LookupVector | ||||||||||

0.5 | 0.55 | 0.6 | 0.65 | 0.7 | 0.75 | 0.8 | 0.85 | 0.9 | 0.95 | 1 |

**Vector C** contains the target rates

Match50 | Match55 | Match60 | Match65 | Match70 | Match75 | Match80 | Match85 | Match90 | Match95 | Match100 |

0.02 | 0.03 | 0.04 | 0.05 | 0.06 | 0.07 | 0.08 | 0.09 | 0.1 | 0.11 | 0.12 |

The output would look like this :

0.08 | 0.07 | 0.07 | 0.06 |

I have solved this using two DO loops (see attached), but I really believe this won't be the most efficient way to do it. This will be repeated many times so removing any loops will be important.

Thanks in advance for any help.

Accepted Solutions

Solution

07-12-2016
04:57 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Andrew_

07-11-2016 10:58 AM

Sure, you need to use the BIN function, which enables you to specify cut points. The values of A are binned into integer indices that correspond to the cut points in B. You can then use those indices to extract the target rates in the C vector.

For reference, you can read about the BIN function in the article "Bin observations by using custom cut points".

```
proc iml;
A = {0.78 0.75 0.72 0.69}; /* values */
B = 0 || do(0.5, 1, 0.05); /* cutpoints */
binIdx = bin(A, B);
C = do(0.02, 0.12, 0.01);
RatesForA = C[,binIdx];
print RatesForA;
```

All Replies

Solution

07-12-2016
04:57 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Andrew_

07-11-2016 10:58 AM

Sure, you need to use the BIN function, which enables you to specify cut points. The values of A are binned into integer indices that correspond to the cut points in B. You can then use those indices to extract the target rates in the C vector.

For reference, you can read about the BIN function in the article "Bin observations by using custom cut points".

```
proc iml;
A = {0.78 0.75 0.72 0.69}; /* values */
B = 0 || do(0.5, 1, 0.05); /* cutpoints */
binIdx = bin(A, B);
C = do(0.02, 0.12, 0.01);
RatesForA = C[,binIdx];
print RatesForA;
```

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

07-12-2016 05:59 AM

Thanks Rick, this solution looks good, it was exactly what I was looking for.

I unfortunately only have SAS 9.2 so I am unable to invoke the BIN function However, I'll retain this for future use.

Much appreciated,

Andrew

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Andrew_

07-12-2016 06:16 AM

Please engage management and IT. SAS 9.2 is more than 8 years old and there have been seven releases of SAS since then. In SAS/IML alone there have been about 50 new functions added.

You can use one DO loop and the LOC function t0 write your own BIN function. Then use the previous solution.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

07-12-2016 06:34 AM

Hi Rick,

Unfortunately I am working a contractor so my weight is pretty low. I've been informed plans for an upgrade are afoot, but it may be after I leave.

As always thanks very much for the support.