Convert a table into a matrix for use with PROC IML

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Convert a table into a matrix for use with PROC IML

[ Edited ]

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 

 


Accepted Solutions
Solution
2 weeks ago
SAS Super FREQ
Posts: 4,042

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 2,517

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

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.

--
Paige Miller
Super User
Posts: 10,520

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

Calling @Rick_SAS

Solution
2 weeks ago
SAS Super FREQ
Posts: 4,042

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

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;
Occasional Contributor
Posts: 8

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

that works! thanks.
Super User
Posts: 10,520

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

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;

Occasional Contributor
Posts: 8

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

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
Super User
Posts: 10,520

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 144 views
  • 4 likes
  • 4 in conversation