0

I have a trouble in converting a matrix into three columns by using SAS.
Here is a reference which use Excel to do that, you can see Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

I have been google and found http://support.sas.com/resources/papers/proceedings13/319-2013.pdf which doing reverse direction (<=).
But I never seen one who is doing (=>).

I do know that I can use some function to get all the rows/column names, by using SAS/SQL & SAS/IML to assign values to each pairwise cell.
However, I would like to look for any function which can help me to finish it more clear and easy to understand.
Note, SAS is the only language I am allowed to use... Sorry that I can't use Excel to do this...

user131605
  • 117
  • 4

1 Answers1

0

You can use array for this:

Case 1: When column names are in a particular order:

data have;
input  col1 $ p1 $ p2 $ p3 $ p4 $;
cards;
f1  x   .   .   .
f2  x   x   .   .
f3  .   x   .   x
f4  .   x   x   .
;
run;


data want;
retain col1 col2 values;
 set have;
 array p[4] $1. p1-p4;
 do i=1 to 4;
 values = p(i);
 col2 = compress("p"||i);
 output;
 end;
 keep col1 col2 values ;
run;

Case 2: When column names are not in a particular order: 

data have1;
input  col1 $ peter $ john $ brian $ mary $;
cards;
f1  x   .   .   .
f2  x   x   .   .
f3  .   x   .   x
f4  .   x   x   .
;
run;



data want1;
retain col1 col2 values;
 set have1;
 array p[4] $5. peter--mary;
 do i=1 to 4;
 values = p(i);
 col2 = VNAME(P(i));
 output;
 end;
 keep col1 col2 values ;
run;

Let me know in case of any queries.

G.Arima
  • 1,171
  • 1
  • 6
  • 13
  • This is what I want! What if my column is not that good? i.e., Peter, John, Brian,... how can I set the array as good as you did? – user131605 Nov 27 '17 at 20:14
  • @user131605 I have updated the answer if your column names are not in a particular order. – G.Arima Nov 28 '17 at 05:27