Turn on suggestions

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

Showing results for

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-06-2015 07:06 AM
(2307 views)

I will need to export the results of what I am now doing in IML to a SAS dataset and thence to Excel and join the data (in another program) to another table by a unique key. Therefore, I need to define unique key values for each row (or column) in each of my matrices and somehow ensure that those numbers do not change as a result of any operations on the matrix and stay together with their corresponding values in other rows (or columns). What is the best way of accomplishing this? I know I can have one matrix serve as row (or column) names for another matrix. Does the answer involve writing my unique key numbers to one matrix (let's call it LOCATION) and set it to be (for example) the row numbers on another matrix, as follows:

rowname = LOCATION

Also, to multiply matrices, the number of rows in the first matrix must equal the number of columns in the second. How do I ensure that my unique key row (or column) is not counted?

- Tags:
- iml
- unique key

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Yes. Put the data in one matrix and the labels/identifiers in another.

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hard to say without knowing what operations you are trying to perform. The observation order won't change unless you perform sorting or subsetting (extracting rows). For those operations, you'll also need to sort or subset the ID variable.

If X is the observation matrix (with N rows and k cols) and beta is the matrix (with k rows) that you are multiplying agains, then the resulting matrix has N rows and the ID variables do not change because Y = X*beta also has N rows.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Suppose that there are two matrices. Let's call them COST, defined as follows,

COST = {12 15 11};

and FREIGHT, below:

FREIGHT = {06050 06005 18020 40071, 1.5 0.7 2.2 1.8, 1.0 3.1 1.9 1.4, 1.7 2.0 2.6 1.5};

The top row in FREIGHT is the unique ID number of (for example) some facility such as a warehouse. How do I make sure that the numbers in the top row are excluded from the calculation when FREIGHT is multiplied by COST? (I'll have to somehow exclude the first row in FREIGHT in any case, otherwise the number of columns in the first matrix won't equal the number of rows in the second). Or should I even include the unique ID number in FREIGHT? Should I, perhaps, make it a separate matrix (let's call it LOCATION) and specify that as the column names for FREIGHT and write the product of FREIGHT and COST to a SAS dataset with LOCATION for column names?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Your problem is transposed from the way most people represent it. Most people represent COST as a column vector, and use the transpose of FREIGHT as a data matrix which has three variables and a row identifier with values '06050', '06005', etc.

One reason that the transposed version is easier to work with is that the ID values in your data cannot be the name of a SAS variable. SAS variables must start with a letter or an underscore; they cannot be numbers. If you transpose, then the ID values can be numbers or character values (your choice).

The following SAS/IML program transposes your data, multiplies it, and then writes it to a SAS data set that has two columns:

```
proc iml;
COST = {12 15 11};
FREIGHT = {06050 06005 18020 40071,
1.5 0.7 2.2 1.8,
1.0 3.1 1.9 1.4,
1.7 2.0 2.6 1.5};
/* transpose data */
C = T(cost);
F = T(FREIGHT);
Location = F[, 1]; /* extract ID column */
X = F[ , 2:nrow(FREIGHT)]; /* call other columns X */
Total = X*C;
create output var {"Location" "Total"};
append;
close output;
quit;
proc print;
format Location Z5.;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks! So, was I right, then, about the need to extract the ID numbers to a separate matrix?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Yes. Put the data in one matrix and the labels/identifiers in another.

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.