Using Array-Changing columns value of '1' with different values for each columns

[Akter]

Hi All, I want to change values of my columns (Q1-5) where there is '1' with different values in each column i.e for Q1, 1 will be remain same as 1 but in col 2, for the value '1' will replace by 2 similarly col3, col 4, col 5 for the value of 1 will value be replaced by 3, 4, 5 as in "data want_1". Finally i want the dataset from wider to longer n(as in Data want_2). Probably using ARRAY:  

Really appreciate your help! Akter

data main;
Input  ID Q1-Q5;
Datalines;
1 1 0 0 0 0
2 0 1 0 0 1
3 0 0 0 1 1
4 1 1 0 0 0
5 0 0 1 0 0
;
run;

Data want_1;
ID Q1 Q2 Q3 Q4 Q5
1  1    0    0    0    0
2  0    2    0    0    5
3  0    0    0    4    5
4  1    2    0    0   0
5  0    0    3    0   0

data want_2:
Id  New_Var
1    1
2    2
2    5
3    4
3    5
4    1
4    2
5    3

[Tom · Accepted solution]

Very simple application of an ARRAY.

data main;
  input id q1-q5;
datalines;
1 1 0 0 0 0
2 0 1 0 0 1
3 0 0 0 1 1
4 1 1 0 0 0
5 0 0 1 0 0
;

data want_1(drop=new_var) want_2(keep=id new_var);
  set main;
  array q q1-q5 ;
  do new_var=1 to dim(q);
    if q[new_var] then do;
       output want_2;
       q[new_var]=new_var;
    end;
  end;
  output want_1;
run;

proc print data=want_1;
  title 'want_1';
run;
proc print data=want_2;
  title 'want_2';
run;
title;

want_1

Obs    id    q1    q2    q3    q4    q5

 1      1     1     0     0     0     0
 2      2     0     2     0     0     5
 3      3     0     0     0     4     5
 4      4     1     2     0     0     0
 5      5     0     0     3     0     0

want_2

Obs    id    new_var

 1      1       1
 2      2       2
 3      2       5
 4      3       4
 5      3       5
 6      4       1
 7      4       2
 8      5       3

[tarheel13]

You can also use proc transpose. I am biased to use it.

proc transpose data=main out=main_t;
	by id;
	var q1-q5;
run;

data main_t2;
	set main_t;
	if col1=0 then delete;
	new_var=substr(_name_,2);
	drop col1 _name_;
run;