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
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;
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.
Calling @Rick_SAS
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.