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-15-2017 10:07 AM
(3571 views)

Hi all,

My SAS dataset looks like following:

Year | country | method | Segment | pr_st1-2 | pr_st1-3 | pr_st1-1 | pr_st2-1 | pr_st2-3 | pr_st2-2 | pr_st3-1 | pr_st3-2 | pr_st3-3 |

2017 | USA | ABC | Retail | 0.2 | 0.6 | 0.2 | 0.3 | 0.6 | 0.1 | 0.1 | 0.3 | 0.6 |

2017 | USA | XYZ | Corporate | 0.1 | 0.5 | 0.4 | 0.2 | 0.6 | 0.2 | 0.2 | 0.3 | 0.5 |

Now, the first four columns are the key, and from each row I need to create a matrix. For example, For '2017 USA ABC Retail' the matrix would be:

state1 | state2 | state3 | |

state1 | 0.2 | 0.2 | 0.6 |

state2 | 0.3 | 0.1 | 0.6 |

state3 | 0.1 | 0.3 | 0.6 |

They are probabilities and would sum to 1. And I would need to create a matrix for each row (with unique keys).

The problem is the number of states would be dynamic and can not be hard-coded. It should depend on the input file. For example, there could be 20 states and then I would have two matrix each with 20x20 dimension, the dimension of the matrix would be square root of number of columns (except the keys).

I relaise the problem has more than one dimension, so for now I'm just looking to be able to create one matrix dynamically from one row. Could you please help me do this using proc iml? Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

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

You will need to extract the row/col indices from the variables names. Here's one possible way putting the final square matrix together using the FULL function:

```
data have;
input Year (country method Segment) ( : $12.)
pr_st1_2 pr_st1_3 pr_st1_1 pr_st2_1 pr_st2_3 pr_st2_2 pr_st3_1 pr_st3_2 pr_st3_3;
datalines;
2017 USA ABC Retail 0.2 0.6 0.2 0.3 0.6 0.1 0.1 0.3 0.6
2017 USA XYZ Corporate 0.1 0.5 0.4 0.2 0.6 0.2 0.2 0.3 0.5
;
run;
proc iml;
use have(keep = pr_st:);
read all var _num_ into x [colname=vn];
rcidx = t( num(scan(vn,3,'t_')) // num(scan(vn,4,'t_')));
do i = 1 to 2;
p = full ( t( x[i,]) || rcidx );
print p;
end;
quit;
```

Storing information in variable names is never a good idea...

8 REPLIES 8

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

Sure.

1. Read the variables that have a common prefix. 'pr_st' (OR you might be able to use READ ALL VAR _NUM_ INTO X;)

2. Read in all variables to a matrix X. Compute n = sqrt(nrow(X)), which should be an integer.

3. The first matrix is x1 = shape(X[1,], n); The second matrix is x2 = shape(X[2,], n);

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

Thanks a lot for the quick reply @Rick_SAS. I understood the overall methodology you are mentioning, and I was able to create the matrices. Additional question: how do I manage the key and the number of matrices?

For example, here it was two rows, and hence two matrices (each nxn, here n=3). Let's say my data is now axb. So I'll have '**a**' different matrices each with **sqrt(b)xsqrt(b)** dimension. How to efficiently create and store them?

```
proc iml;
use A(keep=pr_st:);
read all var _ALL_ into m[colname=c];
x1 = shape(m[1,], sqrt(ncol(m)));
x2 = shape(m[2,], sqrt(ncol(m)));
close A;
```

I'm using this code now, but instead of X1 and X2 it would be X1, X2, ..., Xa matrices.

Thanks again!

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

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

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

You will need to extract the row/col indices from the variables names. Here's one possible way putting the final square matrix together using the FULL function:

```
data have;
input Year (country method Segment) ( : $12.)
pr_st1_2 pr_st1_3 pr_st1_1 pr_st2_1 pr_st2_3 pr_st2_2 pr_st3_1 pr_st3_2 pr_st3_3;
datalines;
2017 USA ABC Retail 0.2 0.6 0.2 0.3 0.6 0.1 0.1 0.3 0.6
2017 USA XYZ Corporate 0.1 0.5 0.4 0.2 0.6 0.2 0.2 0.3 0.5
;
run;
proc iml;
use have(keep = pr_st:);
read all var _num_ into x [colname=vn];
rcidx = t( num(scan(vn,3,'t_')) // num(scan(vn,4,'t_')));
do i = 1 to 2;
p = full ( t( x[i,]) || rcidx );
print p;
end;
quit;
```

Storing information in variable names is never a good idea...

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

Thanks a lot @IanWakeling. This works. Just two questions,

1. How do I get the number of rows of the data set instead of having 2 hard-coded in the line do i = 1 to 2; ?

and

2. Instead of p = full ( t( x[i,]) || rcidx ); if I can use p[i] and call it later? not working now.

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

Just count the number of rows that have been read into the matrix x.

To store the matrices you could use indirect assignment with VALSET and VALUE.

```
proc iml;
use have(keep = pr_st:);
read all var _num_ into x [colname=vn];
rcidx = t( num(scan(vn,3,'t_')) // num(scan(vn,4,'t_')));
n = nrow(x);
pn = 'p1' : 'p'+strip(char(n));
do i = 1 to n;
call valset( pn[i], full ( t( x[i,]) || rcidx ) );
end;
q = value(pn[1]);
print q;
quit;
```

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

After ordering the variable names, you could use SHAPE().

```
data have;
input Year (country method Segment) ( : $12.)
pr_st1_2 pr_st1_3 pr_st1_1 pr_st2_1 pr_st2_3 pr_st2_2 pr_st3_1 pr_st3_2 pr_st3_3;
datalines;
2017 USA ABC Retail 0.2 0.6 0.2 0.3 0.6 0.1 0.1 0.3 0.6
2017 USA XYZ Corporate 0.1 0.5 0.4 0.2 0.6 0.2 0.2 0.3 0.5
;
run;
proc sql noprint;
select name into : vname separated by ' '
from dictionary.columns
where libname='WORK' and memname='HAVE' and lowcase(name) eqt 'pr_st'
order by input(compress(scan(name,-2,'_'),,'kd'),best.),input(compress(scan(name,-1,'_'),,'kd'),best.);
quit;
proc iml;
use have nobs nobs;
read all var {&vname} into x;
close;
do i=1 to nobs;
temp=shape(x[i,],sqrt(ncol(x)));
print temp;
end;
quit;
```

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.