topic Re: Convert a table into a matrix for use with PROC IML in SAS Programming

Convert a table into a matrix for use with PROC IML

SC_1991 — Fri, 13 Apr 2018 13:51:59 GMT

I have a table that that has the 'i', 'j', 'tot' columns and I want to convert this table into a 2x2 matrix with i as the row index and j as the column index and assign the tot as value. Is there a PROC that i can use? i, j range from 1 to 26.

I need to do this so that I can use the resultant matrix and perform matrix operations with PROC IML.

Example:

I J tot

1 1 250

1 2 545

1 3 654

2 1 54

2 2 458

2 3 878

3 1 87

3 2 447

3 3 562

my output should be like :

1 2 3

1 250 545 654

2 54 458 878

3 87 447 562

Re: Convert a table into a matrix for use with PROC IML

PaigeMiller — Thu, 12 Apr 2018 19:09:50 GMT

PROC REPORT will do this.

Define variable I as a GROUP variable and J as an across variable.

There are functions in PROC IML (such as the SHAPE function) that should be able to do this as well, meaning you can skip the intermediate step and go straight to PROC IML.

Re: Convert a table into a matrix for use with PROC IML

Ksharp — Fri, 13 Apr 2018 13:58:23 GMT

Calling @Rick_SAS

Re: Convert a table into a matrix for use with PROC IML

Rick_SAS — Fri, 13 Apr 2018 14:20:29 GMT

You can use the FULL function in SAS/IML to convert the data into a rectangular form:

data Have;
input I      J       tot;
datalines;
1     1      250
1     2      545
1     3      654
2     1       54
2     2      458
2     3      878
3     1       87
3     2      447
3     3      562
;

 proc iml;
use Have;
read all var {tot i j} into S;
close;

X = full(S);
print X;

Re: Convert a table into a matrix for use with PROC IML

Ksharp — Fri, 13 Apr 2018 14:27:16 GMT

data have;
input I      J       tot;
cards;
1     1       250
1     2      545
1     3      654
2     1         54
2     2       458
2     3         878
3     1         87
3     2         447
3     3        562
;

proc iml;
use have;
read all var {i j tot};
close;
x=tot||i||j;
want=full(x);
print want;
quit;

Re: Convert a table into a matrix for use with PROC IML

SC_1991 — Fri, 13 Apr 2018 14:34:05 GMT

is there a way to change the matrix index into
1 2 3
1
2
3

instead of

col1 col2 col 3
row1
row2
row 3

thanks for your help

Re: Convert a table into a matrix for use with PROC IML

SC_1991 — Fri, 13 Apr 2018 14:34:30 GMT

that works! thanks.

Re: Convert a table into a matrix for use with PROC IML

Ksharp — Fri, 13 Apr 2018 14:38:21 GMT

Sure.

data have;
input I      J       tot;
cards;
1     1       250
1     2      545
1     3      654
2     1         54
2     2       458
2     3         878
3     1         87
3     2         447
3     3        562
;

proc iml;
use have;
read all var {i j tot};
close;
x=tot||i||j;
want=full(x);
idx=char(1:nrow(want));
print want[r=idx c=idx l=''];
quit;