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
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.