Clash Royale CLAN TAG#URR8PPP

SAS: turn rows into a matrix

Hi i'm working on a project for summer school and the person teaching likes to work by row. I think it would be beneficial to turn it into a matrix and work with arrays.

The current dataset looks like this:

data longdata;
input var1 var2 Value;
datalines;

rowone colone 1
rowone coltwo 1
rowone colthree 2
rowone colfour 3
rowtwo colone 1
rowtwo coltwo 1
rowtwo colthree 1
rowtwo colfour 2
rowthree colone 3
rowthree coltwo 3
rowthree colthree 3
rowthree colfour 4
rowfour colone 1
rowfour coltwo 3
rowfour colthree 3
rowfour colfour 3
;
run;


I'd like to turn the above into this, preferably through a by statement:

data matrixdata;
input var1-var4;
datalines;

colone coltwo colthree colfour
rowone 1 1 2 2
rowtwo 1 1 1 2
rowthree 3 3 3 4
rowfour 1 3 3 3
;
run;


1 Answer
1

Proc transpose is excellent for transforming categorical data into other layout forms.

Proc transpose

proc transpose data=have out=want;
by var1 notsorted;
id var2;
var value;
run;


Despite looking like an array the data is not implicitly an array. To be a 4x4 array during a single implicit data step iteration the incoming data set would really want 16 variables per row, say v1-v16, with values 1 1 2 3 1 1 1 2 3 3 3 4 1 3 3 3. Each row would correspond one instance of the array.

v1-v16

1 1 2 3 1 1 1 2 3 3 3 4 1 3 3 3

data want2;
retain v1-v16;
array v (16) v1-v16;
set have end=end;
v(_n_) = value;
if end;
keep v:;
run;

data _null_;
set want2;
array m(4,4) v1-v16;
do row = 1 to dim(m,1);
do col = 1 to dim(m,2);
put row= col= m(row,col)=;
end;
end;
run;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.