topic Using Array-Changing columns value of '1' with different values for each columns in SAS Programming

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

Akter — Mon, 21 Jun 2021 18:39:12 GMT

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

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

Tom — Mon, 21 Jun 2021 18:57:15 GMT

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

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

tarheel13 — Mon, 21 Jun 2021 20:41:35 GMT

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;